<?php
class KqbanhangsController extends AppController
{

	var $name = 'Kqbanhangs';
    var $helpers=array('Ctable','Html','Form','Js','Session','FileUpload.FileUpload','Others');
    var $uses=array('Kqbanhang','Chitieunv','Chitieudv','Thoigian','Loaikenh','Phankenh','Zluykenv','Hanghoabhld',
        'Kehoach','Donvi','Thuocdv','Zdv','Nhanvien','Kehoachbhld','Zthbhkenh');
    var $components = array('RequestHandler','FileUpload.FileUpload');
    
    function nhapkqbanhang($nhanvien_id=null)
    {
    	$cur_nhanvien_id = $this->Session->read('cur_manv');
    	if ($nhanvien_id!=$cur_nhanvien_id)
    	{
    		$this->Session->setFlash(__('Không Được Nhập Kết Quả Của Nhân Viên Khác',true));
    		$this->redirect(array('action' => 'hienkqbhngay',$nhanvien_id));
    	}
    	/*
    	 * kiem tra neu chua giao chi tieu tuan thi ko dc nhap
    	 */
    	/*$donvi_id= $this->Session->read('cur_dv');
    	$kh = $this->Kehoach->find('list',array(
    								'conditions'=>array('ngaybd <='=>date('Y-m-d'),
    													'ngaykt >='=>date('Y-m-d'),
    													'DATEDIFF(ngaykt,ngaybd) <='=>7,
    													'donvi_id'=>$donvi_id),
    								'fields'=>array('dagiao')));
    	if (empty($kh) || $kh[key($kh)]==0)
    	{
    		$this->Session->setFlash(__('Không Nhập kết Quả Trước khi Giao Chỉ Tiêu',true));
    		$this->redirect(array('action' => 'hienkqbhngay',$nhanvien_id));
    	}*/
    	
    	/*
    	 * lay chi tieu duoc giao trong tuan
    	 */
    	$thoigian = $this->Kehoach->find('list',array(
    									'recursive'=>0,
    									'conditions'=>array('Kehoach.ngaybd <='=>date('Y-m-d'),
    														'Kehoach.ngaykt >='=>date('Y-m-d'),
    														'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) <='=>7),
    														'donvi_id'=>$this->Session->read('cur_dv'),
    									'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id')
    									));
    	$chitieunvs = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa','Nhanvien'),
    												'conditions'=>array('Kehoach.thoigian_id'=>array_keys($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('Hanghoa.tenhh','soluong','Nhanvien.tennv')));
    	if (count($chitieunvs)!=0)
		{
			$col_ctnv =array_keys($chitieunvs);
			$row_ctnv =Null;
			if (count($col_ctnv)!=0)
			{
				$row_ctnv = array_keys($chitieunvs[$col_ctnv[0]]);
			}
			for ($j=0;$j<count($row_ctnv);$j++)
			{
				for ($i=0;$i<count($col_ctnv);$i++)
				{
					$datanv[$j][$i]= $chitieunvs[$col_ctnv[$i]][$row_ctnv[$j]];
				}
			}
			
		}
		/*
		 * table
		 */
		$ngay = date("Y-m-d");
		$cts = $this->Chitieunv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    											'fields'=>array('id')));
    	$dabc = $this->Kqbanhang->find('list',array(
    												'conditions'=>array('chitieunv_id'=>$cts,'ngay'=>$ngay),
    												'fields'=>array('id')));
    	if (count($dabc)!=0)
    	{
    		$this->Session->setFlash(__('Báo Cáo Hôm Nay Đã Được Lập',true));
    		$this->redirect(array('action' => 'hienkqbhngay',$nhanvien_id));
    	}
    	$chitieus = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    	$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Kenh'),
    											'conditions'=>array('nhanvien_id'=>$nhanvien_id,'ngaykt_hd'=>null),
    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
    												));
    	$lks = $this->Phankenh->find('list',array(
    												'recursive'=>0,'contain'=>array('kenh'),
    												'conditions'=>array('Phankenh.nhanvien_id'=>$nhanvien_id,'ngaykt_hd'=>null),
    												'fields'=>array('Kenh.loaikenh_id'),
    												'group'=>array('Kenh.loaikenh_id')));
    	$loaikenhs = $this->Loaikenh->find('list',array(
    												'conditions'=>array('id'=>$lks),
    												'fields'=>array('id','loaikenh')));
		
    	$this->set(compact('nhanvien_id','col_ctnv','row_ctnv','datanv','thoigian','loaikenhs','kenhs','chitieus'));
    }

    function luukq()
    {
    	$nhanvien_id = $this->Session->read('cur_manv');
		$colnum = count($this->data['Kqbanhang']['col']);
		$rownum = count($this->data['Kqbanhang']['row']);
		for($i=0;$i<$colnum;$i++)
             for($j=0;$j<$rownum;$j++)
             {
					if(isset($this->data['Kqbanhang']['data_tb']['id'][$j][$i]))
						$record['Kqbanhang']['id']=$this->data['Kqbanhang']['data_tb']['id'][$j][$i]; 
             		$record['Kqbanhang']['chitieunv_id']=$this->data['Kqbanhang']['row'][$j];
                    $record['Kqbanhang']['phankenh_id']=$this->data['Kqbanhang']['col'][$i];
                    $record['Kqbanhang']['ngay']=date("Y-m-d");
                    if ($this->data['Kqbanhang']['data_tb'][$j][$i]!=NULL)
                    	$record['Kqbanhang']['soluong']=$this->data['Kqbanhang']['data_tb'][$j][$i];
                    else
                    	$record['Kqbanhang']['soluong']=0;
                    if(!$this->Kqbanhang->saveAll($record))
                    {
                         $this->Session->setFlash(__('Lưu Thất Bại', true));                     
             		}
    		}
    		$this->Session->setFlash(__('Đã Lưu Kết Quả Bán Hàng Ngày '.date('d-m-Y'), true));
       		$this->redirect(array('action' => 'hienkqbhngay',$nhanvien_id));
    }
    
    function hienkqbhngay($nhanvien_id=0)
    {
    	$cur_nhanvien_id = $this->Session->read('cur_manv');
    	$nv =1;
    	if ($nhanvien_id!=$cur_nhanvien_id)
    	{
    		$nv =0;
    	}
    	$ngay = date("Y-m-d");
    	$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
    									));
    	$chitieus = $this->Chitieunv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    											'fields'=>array('id')));
    	$databc = $this->Kqbanhang->find('list',array(
    												'recursive'=>0,'contain'=>array('Chitieunv'),
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('phankenh_id','soluong','Chitieunv.hanghoa_id')));    	
    	$dbc = $this->Kqbanhang->find('list',array(
    												'recursive'=>0,'contain'=>array('Chitieunv'),
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('dabaocao'),'group'=>array('dabaocao')));
    	if (count($dbc)!=0)
    		$dabaocao=$dbc[key($dbc)];
    	else 
    		$dabaocao=0;
    	/*
    	 * lay kenh dc giao cua ngay
    	 */
    	//'OR'=>array('ngaykt_hd >='=>$ngay,'ngaykt_hd'=>null)),
    	$pkngay = $this->Phankenh->find('list',array(
    								'conditions'=>array('nhanvien_id'=>$nhanvien_id,
    													'ngaybd_hd <='=>$ngay,
    													'OR'=>array(array('ngaykt_hd >='=>$ngay),array('ngaykt_hd'=>null))),
    								'fields'=>array('id')));
    	$pkbc = array_keys($pkngay);
    	$lks = $this->Phankenh->find('list',array(
    												'recursive'=>0,'contain'=>array('kenh'),
    												'conditions'=>array('Phankenh.id'=>$pkbc,'ngaykt_hd'=>null),
    												'fields'=>array('Kenh.loaikenh_id'),
    												'group'=>array('Kenh.loaikenh_id')));
    	$loaikenhs = $this->Loaikenh->find('list',array(
    												'conditions'=>array('id'=>$lks),
    												'fields'=>array('id','loaikenh')));
    	
    	$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Kenh'),
    											'conditions'=>array('Phankenh.id'=>$pkbc,'ngaykt_hd'=>null),
    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
    												));
    	$chitieus = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
    	$slkh = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('hanghoa_id','soluong')));
    	/*
    	 * luy ke nv
    	 */
    	$luyke=$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>$nhanvien_id)));
    	$cols[]="Lũy Kế Tuần N";
    	$cols[]="Kế Hoạch Tuần N";
    	$cols[]="% Hoàn Thành Tuần";
    	$cols[]="Kết Quả Tuần N-1";
    	$cols[]="Lũy Kế Tháng";
    	$cols[]="Kế Hoạch Tháng";
    	$cols[]="% Hoàn Thành Tháng";
    	$rows=array();
    	$tongn_1=0;
    	for ($i=0;$i<count($luyke);$i++)
    	{
    		$rows[$luyke[$i]['Hanghoa']['id']]=$luyke[$i]['Hanghoa']['tenhh'];
    	}
    		for($j=0;$j<count($rows);$j++)
    		{
    				$i=0;
    				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN'];
    				$i++;
    				$data[$j][$i]=$luyke[$j]['Zluykenv']['khtuan'];
    				$i++;
    				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhtuan']).'%';
    				$i++;
    				if (!empty($luyke[$j]['Zluykenv']['tongN_1']))
    				{
    					$data[$j][$i]=$luyke[$j]['Zluykenv']['tongN_1'];
    					$tongn_1 =$data[$j][$i]; 
    				}
    				else
    				{ 
    					$data[$j][$i] = 0;
    					$tongn_1 = 0;
    				}
    				
    				$i++;
    				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN']+$tongn_1;
    				$i++;
    				$data[$j][$i]=$luyke[$j]['Zluykenv']['khthang'];
    				$i++;
    				$data[$j][$i]=ROUND($luyke[$j]['Zluykenv']['hoanthanhthang'],2).'%';
    				
    		}
    	$nhanvieninfo = $this->Phankenh->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	$this->set(compact('cols','rows','data','nhanvieninfo'));
    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','dabaocao','slkh','nhanvien_id','nv'));
    }
    
    function suakqbanhang($nhanvien_id=0)
    {
    	$ngay = date("Y-m-d");
    	$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
    									));
    	$chitieus = $this->Chitieunv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    											'fields'=>array('id')));
    	$dabc = $this->Kqbanhang->find('list',array(
    												'conditions'=>array('chitieunv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('dabaocao')));
    	$dabaocao = $dabc[key($dabc)];
    	if ($dabaocao==1)
    	{
    		$this->Session->setFlash(__('Kết Quả Đã Báo Cáo Không Được Sửa',true));
    		$this->redirect(array('action' => 'hienkqbhngay',$nhanvien_id));
    	}
    	$data = $this->Kqbanhang->find('list',array(
    												'conditions'=>array('chitieunv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('phankenh_id','soluong','chitieunv_id')));
    	$data_id = $this->Kqbanhang->find('list',array(
    												'conditions'=>array('chitieunv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('phankenh_id','id','chitieunv_id')));
    	$lks = $this->Phankenh->find('list',array(
    												'recursive'=>0,'contain'=>array('kenh'),
    												'conditions'=>array('Phankenh.nhanvien_id'=>$nhanvien_id,'ngaykt_hd'=>null),
    												'fields'=>array('Kenh.loaikenh_id'),
    												'group'=>array('Kenh.loaikenh_id')));
    	$loaikenhs = $this->Loaikenh->find('list',array(
    												'conditions'=>array('id'=>$lks),
    												'fields'=>array('id','loaikenh')));
    	
    	$kenhs = $this->Phankenh->find('list',array(
    											'recursive'=>0,'contain'=>array('Kenh'),
    											'conditions'=>array('nhanvien_id'=>$nhanvien_id,'ngaykt_hd'=>null),
    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
    												));
    	$chitieus = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('id','Hanghoa.tenhh')));
    	$this->set(compact('nhanvien_id','data','data_id','loaikenhs','kenhs','chitieus'));
    }
    
    function baocaokq($nhanvien_id=0)
    {
    	$this->layout='ajax';
    	$ngay = date("Y-m-d");
    	$thoigian = $this->Thoigian->find('list',array(
    									'conditions'=>array('ngaybd <='=>date('Y-m-d'), 'ngaykt >='=>date('Y-m-d'),'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
    									));
    	$chitieus = $this->Chitieunv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
    											'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    											'fields'=>array('id')));
    	$data = $this->Kqbanhang->find('list',array(
    												'conditions'=>array('chitieunv_id'=>$chitieus,'ngay'=>$ngay),
    												'fields'=>array('id')));
    	if (count($data)!=0)
    	{
    		foreach ($data as $value):
    			$this->Kqbanhang->id=$value;
    			$this->Kqbanhang->saveField('dabaocao',1,false);
    		endforeach;
    	}
    }    
    
    function luyke($nhanvien_id=null)
    {
    	if ($nhanvien_id)
    	{
	    	$nhanvieninfo = $this->Phankenh->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
	    	$luyke=$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>$nhanvien_id)));
	    	$cols[]="Lũy Kế Tuần N";
	    	$cols[]="Kế Hoạch Tuần N";
	    	$cols[]="% Hoàn Thành Tuần";
	    	$cols[]="Kết Quả Tuần N-1";
	    	$cols[]="Lũy Kế Tháng";
	    	$cols[]="Kế Hoạch Tháng";
	    	$cols[]="% Hoàn Thành Tháng";
	    	$rows= array();
	    	for ($i=0;$i<count($luyke);$i++)
	    	{
	    		$rows[$luyke[$i]['Hanghoa']['id']]=$luyke[$i]['Hanghoa']['tenhh'];
	    	}
	    	for($j=0;$j<count($rows);$j++)
	    	{
	    				$i=0;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN'];
	    				$i++;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['khtuan'];
	    				$i++;
	    				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhtuan']).'%';
	    				$i++;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['tongN_1'];
	    				$i++;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN']+$luyke[$j]['Zluykenv']['tongN_1'];
	    				$i++;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['khthang'];
	    				$i++;
	    				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhthang']).'%';
	    				
	    	}
	    	$this->set(compact('cols','rows','data','nhanvieninfo'));
    	}
    	else 
    	{
    		$this->redirect(array('controller'=>'pages','action' => 'home'));
    	}
        $this->set(compact('nhanvien_id'));
    	
    	
    }
    
    function luykeexcel($nhanvien_id=null)
    {
        $this->layout='';
        $iddonvi=$this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id),
                'fields'=>array('donvi_id','donvi_id'),'recursive'=>-1));
         $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$iddonvi),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
        
    	if ($nhanvien_id)
    	{
	    	$nhanvieninfo = $this->Phankenh->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
	    	$luyke=$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>$nhanvien_id)));
	    	$cols[]="Lũy Kế Tuần N";
	    	$cols[]="Kế Hoạch Tuần N";
	    	$cols[]="% Hoàn Thành Tuần";
	    	$cols[]="Kết Quả Tuần N-1";
	    	$cols[]="Lũy Kế Tháng";
	    	$cols[]="Kế Hoạch Tháng";
	    	$cols[]="% Hoàn Thành Tháng";
	    	$rows= array();
	    	for ($i=0;$i<count($luyke);$i++)
	    	{
	    		$rows[$luyke[$i]['Hanghoa']['id']]=$luyke[$i]['Hanghoa']['tenhh'];
	    	}
	    	for($j=0;$j<count($rows);$j++)
	    	{
	    				$i=0;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN'];
	    				$i++;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['khtuan'];
	    				$i++;
	    				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhtuan']).'%';
	    				$i++;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['tongN_1'];
	    				$i++;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN']+$luyke[$j]['Zluykenv']['tongN_1'];
	    				$i++;
	    				$data[$j][$i]=$luyke[$j]['Zluykenv']['khthang'];
	    				$i++;
	    				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhthang']).'%';
	    				
	    	}
	    	$this->set(compact('cols','rows','data','nhanvieninfo'));
    	}
    	else 
    	{
    		$this->redirect(array('controller'=>'pages','action' => 'home'));
    	}
    	
    	
    }
    
    function kqbhthangnv_1($nhanvien_id=0)
    {
    	$kehoachth = $this->Chitieunv->find('list',array(
    										'recursive'=>0,'contain'=>array('kehoach'),
    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id),
    										'fields'=>array('kehoach_id')));
    	$tg = $this->Chitieunv->Kehoach->find('list',array(
    												'recursive'=>0,'contain'=>array('Thoigian'),
    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id')
    												));////'Thoigian.ngaykt'
    	foreach ($tg as $key => $value):
    		foreach ($value as $key1 => $value1):
    			$thoigian[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
    		endforeach;
    	endforeach;    	
    	$this->set(compact('thoigian','nhanvien_id'));
    }
    
    function kqbhtuannv_1($nhanvien_id=0)
    {
    	$kehoachth = $this->Chitieunv->find('list',array(
    										'recursive'=>0,'contain'=>array('kehoach'),
    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id),
    										'fields'=>array('kehoach_id'),
    										'group'=>array('kehoach_id')));
    	$tg = $this->Chitieunv->Kehoach->find('list',array(
    												'recursive'=>0,'contain'=>array('Thoigian'),
    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id'),
    												'group'=>array('Thoigian.id')));////'Thoigian.ngaykt'
    	$thoigian = array();
    	foreach ($tg as $key => $value):
    		foreach ($value as $key1 => $value1):
    			$thoigian[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
    		endforeach;
    	endforeach;
    	$this->set(compact('thoigian','nhanvien_id'));
    }
    
   
    function laykqbh_1($nhanvien_id=0)
    {
    	$this->layout = 'ajax';
    	if (!empty($this->data['Kqbanhang']['thoigian_id'])|| !empty($this->data['thoigian_id']))
    	{
    		if (!empty($this->data['Kqbanhang']['thoigian_id']))
	    		$thoigian_id=$this->data['Kqbanhang']['thoigian_id'];
	    	else
	    		$thoigian_id=$this->data['thoigian_id'];
	    	
	    	$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id),));
	    	if ($thoigian[0]['Thoigian']['thoigian_id']==0)
				$kh = $this->Kehoach->find('list',array(
											'recursive'=>0,'contain'=>array('Thoigian'),
											'conditions'=>array('Thoigian.thoigian_id'=>$thoigian_id)));
			else 
				$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
	    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
			$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
	    	$data = $this->Kqbanhang->find('all',array(
	    												'recursive'=>0,'contain'=>array('Chitieunv'),
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
	    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
	    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
	    	for($i=0;$i<count($data);$i++)
	    	{
	    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
		  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
	    	}
	    	$lks = $this->Phankenh->find('list',array(
	    												'recursive'=>0,'contain'=>array('kenh'),
	    												'conditions'=>array('Phankenh.nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('Kenh.loaikenh_id'),
	    												'group'=>array('Kenh.loaikenh_id')));
	    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
	    	
	    	$kenhs = $this->Phankenh->find('list',array(
	    											'recursive'=>0,'contain'=>array('Kenh'),
	    											'conditions'=>array('nhanvien_id'=>$nhanvien_id),
	    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
	    												));
	    	$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Hanghoa'),
	    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
	    												));
    	}
    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','nhanvien_id'));
    }

    //backup
	function laykqbh_2($nhanvien_id=0)
    {
    	$this->layout = 'ajax';
    	if (!empty($this->data['Kqbanhang']['thoigian_id'])|| !empty($this->data['thoigian_id']))
    	{
    		if (!empty($this->data['Kqbanhang']['thoigian_id']))
	    		$thoigian_id=$this->data['Kqbanhang']['thoigian_id'];
	    	else
	    		$thoigian_id=$this->data['thoigian_id'];
	    	
	    	$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id),));
	    	if ($thoigian[0]['Thoigian']['thoigian_id']==0)
				$kh = $this->Kehoach->find('list',array(
											'recursive'=>0,'contain'=>array('Thoigian'),
											'conditions'=>array('Thoigian.thoigian_id'=>$thoigian_id)));
			else 
				$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
	    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
			$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
	    	$chitieus = $this->Chitieunv->find('list',array(
	    											'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
	    											'fields'=>array('id')));
	    	$data = $this->Kqbanhang->find('all',array(
	    												'recursive'=>0,'contain'=>array('Chitieunv'),
	    												'conditions'=>array('chitieunv_id'=>$chitieus,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
	    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
	    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
	    	for($i=0;$i<count($data);$i++)
	    	{
	    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
		  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
	    	}
	    	$lks = $this->Phankenh->find('list',array(
	    												'recursive'=>0,'contain'=>array('kenh'),
	    												'conditions'=>array('Phankenh.nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('Kenh.loaikenh_id'),
	    												'group'=>array('Kenh.loaikenh_id')));
	    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
	    	
	    	$kenhs = $this->Phankenh->find('list',array(
	    											'recursive'=>0,'contain'=>array('Kenh'),
	    											'conditions'=>array('nhanvien_id'=>$nhanvien_id),
	    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
	    												));
	    	$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Hanghoa'),
	    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
	    												));
    	}
    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','nhanvien_id'));
    }
    
  	function kqbhngaynv_2($nhanvien_id=0)
    {
    	$this->set(compact('nhanvien_id'));
    }
    
    function kqngaynv_2($nhanvien_id=null)
    {
    	if($nhanvien_id!=null)
    	{
    	$date = $this->data['Kqbanhang']['ngay'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	$ngay=date('Y-m-d',strtotime($date));
	    	$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'thoigian_id !='=>0)
	    									));
	    	$dbc = $this->Kqbanhang->find('list',array(
	    											'recursive'=>0,'contain'=>array('Chitieunv'),
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('dabaocao'),'group'=>array('dabaocao')));
	    	if (count($dbc)!=0)
	    		$dbc=$dbc[key($dbc)];
	    	else
	    		$dbc=2;
	    	$databc = $this->Kqbanhang->find('list',array(
	    												'recursive'=>0,'contain'=>array('Chitieunv'),
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
	    												'fields'=>array('phankenh_id','soluong','Chitieunv.hanghoa_id'),
	    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
	    	//'chitieunv_id'=>$chitieus
	    	$lks = $this->Phankenh->find('list',array(
	    												'recursive'=>0,'contain'=>array('kenh'),
	    												'conditions'=>array('Phankenh.nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('Kenh.loaikenh_id'),
	    												'group'=>array('Kenh.loaikenh_id')));
	    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
	    	
	    	$kenhs = $this->Phankenh->find('list',array(
	    											'recursive'=>0,'contain'=>array('Kenh'),
	    											'conditions'=>array('nhanvien_id'=>$nhanvien_id),
	    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
	    												));
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
    	}
			$this->set(compact('databc','loaikenhs','kenhs','chitieus','dbc','nhanvien','date'));
    }
    
    /*
     * tong hop ket qua ban hang cua trung tam tu kq cua nhan vien
     */
    
    function kqbhngaytt($dv_id=0)
    {
    	$this->set(compact('dv_id'));
    	
    } 
    
    function kqngaytt($dv_id)
    {
    	$ngay=$ngay=date('Y-m-d',strtotime($this->data['Kqbanhang']['ngay']));;
    	$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)));
    	// rows
    	$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Kehoach.donvi_id'=>$dv_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
    	
    	$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$dv_id),'fields'=>array('id')));
    	
    	//cols
    	$lkbc = $this->Kqbanhang->find('list',array(
    									'recursive'=>0,
    									'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'ngay'=>$ngay),
    									'fields'=>array('phankenh_id'),
    									'group'=>array('Phankenh.id')));
    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,'contain'=>array('kenh'),
	    										'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'Phankenh.id'=>$lkbc),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
    	
	    										
    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    	$khnv = $this->Chitieunv->find('all',array(
	    												'recursive'=>0,
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),
	    																	'Kehoach.donvi_id'=>$dv_id),
	    												'fields'=>array('hanghoa_id','sum(soluong) as slkh'),
    													'group'=>'hanghoa_id'));
    	for($i=0;$i<count($khnv);$i++)
    	{
    		$slkh[$khnv[$i]['Chitieunv']['hanghoa_id']]=$khnv[$i][0]['slkh'];
    	}
    	$loaikenhs['tong']="Tổng";
    	$loaikenhs['chitieu']="Chỉ Tiêu Tuần";
    	$loaikenhs['hoanthanh']="% Hoàn Thành";
    	$qstr='SELECT k.loaikenh_id, ct.hanghoa_id, sum( kq.soluong ) as soluong
				FROM  kqbanhangs kq, phankenhs pk, kenhs k, chitieunvs ct, nhanviens nv
				WHERE kq.phankenh_id = pk.id
					AND pk.kenh_id = k.id
					AND kq.chitieunv_id = ct.id
					AND ct.nhanvien_id = nv.id
					AND nv.donvi_id ='.$dv_id .'
					 AND kq.ngay = "'.$ngay.'" GROUP BY k.loaikenh_id, ct.hanghoa_id';
    		$dt=$this->Kqbanhang->query($qstr);
    		
    		if (!empty($dt))
    		{
    			$row=0;
    			$col=0;
	    		foreach ($chitieus as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['k']['loaikenh_id'])
	    					{
	    						$data[$row][$col]=$dt[$i][0]['soluong'];
	    						$tong +=$data[$row][$col];
	    					}
	    				endfor;
	    				$col++;
	    			endforeach;
	    			$data[$row][--$col]=round($tong*100/$slkh[$keyct],2)."%";
	    			$data[$row][--$col]=$slkh[$keyct];
	    			$data[$row][--$col]=$tong;
	    			$row++;
	    		endforeach;
    		};
    	$this->set(compact('chitieus','loaikenhs','data','dv_id','ngay'));
    	
    }

    function kqngayttexcel($dv_id,$ngay)
    {
        $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$dv_id),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
        $this->layout='';
        $d1=$this->Donvi->find('first',array(
                'conditions'=>array('id'=>$dv_id),
                'fields'=>array('tendv'),
                'recursive'=>-1));
    	//$ngay=$ngay=date('Y-m-d',strtotime($this->data['Kqbanhang']['ngay']));;
    	$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)));
    	// rows
    	$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'Kehoach.donvi_id'=>$dv_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
    	
    	$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$dv_id),'fields'=>array('id')));
    	
    	//cols
    	$lkbc = $this->Kqbanhang->find('list',array(
    									'recursive'=>0,
    									'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'ngay'=>$ngay),
    									'fields'=>array('phankenh_id'),
    									'group'=>array('Phankenh.id')));
    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,'contain'=>array('kenh'),
	    										'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'Phankenh.id'=>$lkbc),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
    	
	    										
    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    	$khnv = $this->Chitieunv->find('all',array(
	    												'recursive'=>0,
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),
	    																	'Kehoach.donvi_id'=>$dv_id),
	    												'fields'=>array('hanghoa_id','sum(soluong) as slkh'),
    													'group'=>'hanghoa_id'));
    	for($i=0;$i<count($khnv);$i++)
    	{
    		$slkh[$khnv[$i]['Chitieunv']['hanghoa_id']]=$khnv[$i][0]['slkh'];
    	}
    	$loaikenhs['tong']="Tổng";
    	$loaikenhs['chitieu']="Chỉ Tiêu Tuần";
    	$loaikenhs['hoanthanh']="% Hoàn Thành";
    	$qstr='SELECT k.loaikenh_id, ct.hanghoa_id, sum( kq.soluong ) as soluong
				FROM  kqbanhangs kq, phankenhs pk, kenhs k, chitieunvs ct, nhanviens nv
				WHERE kq.phankenh_id = pk.id
					AND pk.kenh_id = k.id
					AND kq.chitieunv_id = ct.id
					AND ct.nhanvien_id = nv.id
					AND nv.donvi_id ='.$dv_id .'
					 AND kq.ngay = "'.$ngay.'" GROUP BY k.loaikenh_id, ct.hanghoa_id';
    		$dt=$this->Kqbanhang->query($qstr);
    		
    		if (!empty($dt))
    		{
    			$row=0;
    			$col=0;
	    		foreach ($chitieus as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['k']['loaikenh_id'])
	    					{
	    						$data[$row][$col]=$dt[$i][0]['soluong'];
	    						$tong +=$data[$row][$col];
	    					}
	    				endfor;
	    				$col++;
	    			endforeach;
	    			$data[$row][--$col]=round($tong*100/$slkh[$keyct],2)."%";
	    			$data[$row][--$col]=$slkh[$keyct];
	    			$data[$row][--$col]=$tong;
	    			$row++;
	    		endforeach;
    		};
    	$this->set(compact('chitieus','loaikenhs','data','dv_id','ngay','d1'));
    	
    }

    function kqbhtuantt($dv_id=0)
    {
    	$nam = $this->Chitieunv->Kehoach->find('list',array(
	    										'conditions'=>array('donvi_id'=>$dv_id),
	    										'fields'=>array('ngaybd'),
	    										'group'=>array('YEAR(Kehoach.ngaybd)'),
	    										'order'=>array('ngaybd DESC')));
    		foreach ($nam as $key => $value) 
    		{
    			$nams[date('Y',strtotime($value))]=date('Y',strtotime($value)); 
    		}
    	$namht = date('Y');
    	$kh = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Chitieudv.donvi_id'=>$dv_id,'YEAR(Kehoach.ngaybd)'=>$namht),
    											'fields'=>array('Kehoach.thoigian_id'),
    											'group'=>array('Kehoach.thoigian_id')));	
    	$thoigian=$this->Thoigian->find('list',array('conditions'=>array('id'=>$kh)));
    	$this->set(compact('nams','thoigian','dv_id'));
    }

    function laytuantt1($dv_id=0)
    {
    	if (!empty($this->data['Kqbanhang']['thang']) || !empty($this->data['thang']))
    	{
    		if (!empty($this->data['Kqbanhang']['thang']))
	    		$thang_id=$this->data['Kqbanhang']['thang'];
	    	else
	    			$thang_id=$this->data['thang'];
	    	$nam = $this->data['Kqbanhang']['nam'];
	    	$tg_cha = $this->Thoigian->find('list',array(
	    									'conditions'=>array('id'=>$thang_id),
	    									'fields'=>array('ngaybd','ngaykt')));
	    	$tg = $this->Chitieunv->find('list',array(
		    								'recursive'=>0,
		    								'conditions'=>array('Kehoach.donvi_id'=>$dv_id,
		    													'Kehoach.ngaybd >= '=>key($tg_cha),
		    													'Kehoach.ngaykt <=' =>$tg_cha[key($tg_cha)],
		    													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) <='=>7),
		    								'fields'=>array('Kehoach.ngaybd','Kehoach.ngaykt','Kehoach.thoigian_id'),
		    								'group'=>array('Kehoach.ngaybd'),
		    								'order'=>array('ngaybd DESC')));
	    	/*$tg = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd >= '=>key($tg_cha),
	    														'ngaykt <=' =>$tg_cha[key($tg_cha)],
	    														'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7,
	    														'YEAR(ngaybd)'=>$nam),
	    									'fields'=>array('ngaybd','ngaykt','id'),
	    									'order'=>array('ngaybd DESC')));*/
	    	foreach ($tg as $key => $value):
    			foreach ($value as $key1 => $value1):
    				$thoigian[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
    			endforeach;
    		endforeach;    	
    	}
    	$this->set(compact('thoigian','dv_id'));
    }
    	
	function laykqbhtt1($dv_id=0)
    {
    	$this->layout = 'ajax';
    	if (!empty($this->data['Kqbanhang']['thoigian_id'])|| !empty($this->data['thoigian_id'])||!empty($this->data['Kqbanhang']['thang'])|| !empty($this->data['thang']))
    	{
    		if (!empty($this->data['Kqbanhang']['thoigian_id']))
	    		$thoigian_id=$this->data['Kqbanhang']['thoigian_id'];
	    	elseif (!empty($this->data['thoigian_id']))
	    		$thoigian_id=$this->data['thoigian_id'];
	    	elseif(!empty($this->data['Kqbanhang']['thang']))
	    		$thoigian_id=$this->data['Kqbanhang']['thang'];
	    	else
	    		$thoigian_id=$this->data['thang'];
	    		
	    	$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id),));
	    	$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$dv_id),'fields'=>array('id')));
	    	
    		$nbd =$thoigian[0]['Thoigian']['ngaybd'];
	    	$nkt = $thoigian[0]['Thoigian']['ngaykt'];
	    	
	    	if (date('d-m-Y',strtotime($nkt))-date('d-m-Y',strtotime($nbd))>8)
	    	{
	    		$khct = $this->Chitieunv->find('all',array(
	    									'recursive'=>0,
	    									'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,
	    														'Kehoach.donvi_id'=>$dv_id),
	    									'fields'=>array('hanghoa_id','sum(Chitieunv.soluong) as slkh'),
	    									'group'=>array('hanghoa_id'),
	    									'order'=>array('hanghoa_id')));
	    		$tg = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd >= '=>$nbd,'ngaykt <=' =>$nkt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7),
	    									'fields'=>array('id')));
				$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$tg)));
	    	$title = "Chỉ Tiêu Tháng";
	    	}
			else
			{
				$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
				$khct =$this->Chitieunv->find('all',array(
	    										'recursive'=>0,'contain'=>array('Hanghoa','Nhanvien'),
	    										'conditions'=>array('kehoach_id'=>$kh,'Kehoach.donvi_id'=>$dv_id),
	    										'fields'=>array('hanghoa_id','sum(Chitieunv.soluong) as slkh'),
	    										'group'=>array('hanghoa_id'),
	    										'order'=>array('hanghoa_id')));
			$title = "Chỉ Tiêu Tuần";
			}
			//row
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Hanghoa','Nhanvien'),
	    												'conditions'=>array('kehoach_id'=>$kh,'Nhanvien.donvi_id'=>$dv_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
														'order'=>array('hanghoa_id')
	    												));
	    	//col
	    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
			$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
			
	    	$lkbc = $this->Kqbanhang->find('list',array(
    									'recursive'=>0,
    									'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    									'fields'=>array('phankenh_id'),
    									'group'=>array('Phankenh.id')));
	    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,'contain'=>array('kenh'),
	    										'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'Phankenh.id'=>$lkbc),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
    		$loaikenhs = $loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    		$loaikenhs[]="Tổng";
    		$loaikenhs[]=$title;
    		$loaikenhs[]="% Hoàn Thành";
    		//data
	    	
			$qstr = 'SELECT k.loaikenh_id, ct.hanghoa_id, sum( kq.soluong ) as soluong
					FROM kqbanhangs kq, phankenhs pk, kenhs k, chitieunvs ct, nhanviens nv
					WHERE kq.phankenh_id = pk.id
					AND pk.kenh_id = k.id
					AND kq.chitieunv_id = ct.id
					AND ct.nhanvien_id = nv.id
					AND nv.donvi_id ='.$dv_id.'
					AND kq.ngay between "'.$ngaybd.'" and "'.$ngaykt.'" 
					GROUP BY k.loaikenh_id, ct.hanghoa_id';
			$dt = $this->Kqbanhang->query($qstr);	    	
	    	$row=0;
    		$col=0;
    		if(!empty($dt))
    		{
	    		foreach ($chitieus as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['k']['loaikenh_id'])
	    					{ 
	    						$data[$row][$col]=$dt[$i][0]['soluong'];
	    						$tong +=$data[$row][$col];
	    					} 
	    				endfor;
	    				$col++;
	    			endforeach;
	    			$data[$row][--$col]=round($tong*100/$khct[$row][0]['slkh'],2).'%';
	    			$data[$row][--$col]=$khct[$row][0]['slkh'];
	    			$data[$row][--$col]=$tong;
	    			$row++;
	    		endforeach;
    		}
    	}
    	$this->set(compact('data','loaikenhs','chitieus','dv_id','thoigian_id'));
    }
    
    function laykqbhttexcel($dv_id=0,$thoigian_id)
    {
    	$this->layout = '';
        
        $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$dv_id),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
        
        
    	if (!empty($thoigian_id))
    	{
    			
            $d1=$this->Donvi->find('first',array(
                'conditions'=>array('id'=>$dv_id),
                'fields'=>array('tendv'),
                'recursive'=>-1));
	    	$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id),));
	    	$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$dv_id),'fields'=>array('id')));
	    	
    		$nbd =$thoigian[0]['Thoigian']['ngaybd'];
	    	$nkt = $thoigian[0]['Thoigian']['ngaykt'];
	    	
	    	if (date('d-m-Y',strtotime($nkt))-date('d-m-Y',strtotime($nbd))>8)
	    	{
	    		$khct = $this->Chitieunv->find('all',array(
	    									'recursive'=>0,
	    									'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,
	    														'Kehoach.donvi_id'=>$dv_id),
	    									'fields'=>array('hanghoa_id','sum(Chitieunv.soluong) as slkh'),
	    									'group'=>array('hanghoa_id'),
	    									'order'=>array('hanghoa_id')));
	    		$tg = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd >= '=>$nbd,'ngaykt <=' =>$nkt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7),
	    									'fields'=>array('id')));
				$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$tg)));
	    	$title = "Chỉ Tiêu Tháng";
	    	}
			else
			{
				$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
				$khct =$this->Chitieunv->find('all',array(
	    										'recursive'=>0,'contain'=>array('Hanghoa','Nhanvien'),
	    										'conditions'=>array('kehoach_id'=>$kh,'Kehoach.donvi_id'=>$dv_id),
	    										'fields'=>array('hanghoa_id','sum(Chitieunv.soluong) as slkh'),
	    										'group'=>array('hanghoa_id'),
	    										'order'=>array('hanghoa_id')));
			$title = "tuần";
			}
			//row
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Hanghoa','Nhanvien'),
	    												'conditions'=>array('kehoach_id'=>$kh,'Nhanvien.donvi_id'=>$dv_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
														'order'=>array('hanghoa_id')
	    												));
	    	//col
	    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
			$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
			
	    	$lkbc = $this->Kqbanhang->find('list',array(
    									'recursive'=>0,
    									'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    									'fields'=>array('phankenh_id'),
    									'group'=>array('Phankenh.id')));
	    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,'contain'=>array('kenh'),
	    										'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'Phankenh.id'=>$lkbc),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
    		$loaikenhs = $loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    		$loaikenhs[]="Tổng";
    		$loaikenhs[]=$title;
    		$loaikenhs[]="% Hoàn Thành";
    		//data
	    	
			$qstr = 'SELECT k.loaikenh_id, ct.hanghoa_id, sum( kq.soluong ) as soluong
					FROM kqbanhangs kq, phankenhs pk, kenhs k, chitieunvs ct, nhanviens nv
					WHERE kq.phankenh_id = pk.id
					AND pk.kenh_id = k.id
					AND kq.chitieunv_id = ct.id
					AND ct.nhanvien_id = nv.id
					AND nv.donvi_id ='.$dv_id.'
					AND kq.ngay between "'.$ngaybd.'" and "'.$ngaykt.'" 
					GROUP BY k.loaikenh_id, ct.hanghoa_id';
			$dt = $this->Kqbanhang->query($qstr);	    	
	    	$row=0;
    		$col=0;
    		if(!empty($dt))
    		{
	    		foreach ($chitieus as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['k']['loaikenh_id'])
	    					{ 
	    						$data[$row][$col]=$dt[$i][0]['soluong'];
	    						$tong +=$data[$row][$col];
	    					} 
	    				endfor;
	    				$col++;
	    			endforeach;
	    			$data[$row][--$col]=round($tong*100/$khct[$row][0]['slkh'],2).'%';
	    			$data[$row][--$col]=$khct[$row][0]['slkh'];
	    			$data[$row][--$col]=$tong;
	    			$row++;
	    		endforeach;
    		}
    	}
    	$this->set(compact('data','loaikenhs','chitieus','dv_id','d1','nbd','nkt'));
    }

    function kqbhthangtt($dv_id=0)
    {
    	$nam = $this->Chitieunv->Kehoach->find('list',array(
	    										'conditions'=>array('donvi_id'=>$dv_id),
	    										'fields'=>array('ngaybd'),
	    										'group'=>array('YEAR(Kehoach.ngaybd)'),
	    										'order'=>array('ngaybd DESC')));
    		foreach ($nam as $key => $value) 
    		{
    			$nams[date('Y',strtotime($value))]=date('Y',strtotime($value)); 
    		}
    		$namht = date('Y');
    		$kh = $this->Chitieudv->find('list',array(
    											'recursive'=>0,'contain'=>array('Kehoach'),
    											'conditions'=>array('Chitieudv.donvi_id'=>$dv_id),
    											'fields'=>array('Kehoach.thoigian_id'),
    											'group'=>array('Kehoach.thoigian_id')));	
    	$thoigian=$this->Thoigian->find('list',array('conditions'=>array('id'=>$kh),'order'=>array('ngaybd DESC')));
    	$this->set(compact('thoigian','dv_id','nams'));
    }

    /*
     * nhan vien
     */
	function kqbhngaynv($donvi_id=null,$nhanvien_id=0)
    {
    	if ($donvi_id!=null)
    	{
    		$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id)));
    	}
    	$this->set(compact('donvi_id','nhanvien_id','nhanvien'));
    }

	function kqngaynv1()
    {
    	$this->layout = 'ajax';
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvien'];
    	$date = $this->data['Kqbanhang']['ngay'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	$ngay=date('Y-m-d',strtotime($date));
	    	$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
	    									));
	    	$dbc = $this->Kqbanhang->find('list',array(
	    											'recursive'=>0,'contain'=>array('Chitieunv'),
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('dabaocao'),'group'=>array('dabaocao')));
	    	if (count($dbc)!=0)
	    		$dbc=$dbc[key($dbc)];
	    	else
	    		$dbc=2;
	    	$databc = $this->Kqbanhang->find('list',array(
	    												'recursive'=>0,'contain'=>array('Chitieunv'),
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
	    												'fields'=>array('phankenh_id','soluong','Chitieunv.hanghoa_id'),
	    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
	    	if (!empty($databc))
	    	{
	    	$pkbc = array_keys($databc[key($databc)]);
	    	//'chitieunv_id'=>$chitieus
	    	$lks = $this->Phankenh->find('list',array(
	    												'recursive'=>0,'contain'=>array('kenh'),
	    												'conditions'=>array('Phankenh.id'=>$pkbc),
	    												'fields'=>array('Kenh.loaikenh_id'),
	    												'group'=>array('Kenh.loaikenh_id')));//,'ngaykt_hd'=>null
	    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
	    	
	    	$kenhs = $this->Phankenh->find('list',array(
	    											'recursive'=>0,'contain'=>array('Kenh'),
	    											'conditions'=>array('Phankenh.id'=>$pkbc),
	    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
	    												));
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
			$slkh = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('hanghoa_id','soluong')));
	    	
			$this->set(compact('databc','loaikenhs','kenhs','chitieus','dbc','nhanvien','date','slkh'));
	    	}
      $this->set(compact('nhanvien_id','date'));
    }

    function kqngaynvexcel($nhanvien_id,$date)
    {
        
        $nv=$this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id),
            'fields'=>array('donvi_id','donvi_id'),'recursive'=>-1));
        $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$nv),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
        
       
    	$this->layout = '';
    	//$nhanvien_id = $this->data['Kqbanhang']['nhanvien'];
    	//$date = $this->data['Kqbanhang']['ngay'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	$ngay=date('Y-m-d',strtotime($date));
	    	$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
	    									));
	    	$dbc = $this->Kqbanhang->find('list',array(
	    											'recursive'=>0,'contain'=>array('Chitieunv'),
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('dabaocao'),'group'=>array('dabaocao')));
	    	if (count($dbc)!=0)
	    		$dbc=$dbc[key($dbc)];
	    	else
	    		$dbc=2;
	    	$databc = $this->Kqbanhang->find('list',array(
	    												'recursive'=>0,'contain'=>array('Chitieunv'),
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
	    												'fields'=>array('phankenh_id','soluong','Chitieunv.hanghoa_id'),
	    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
	    	if (!empty($databc))
	    	{
	    	$pkbc = array_keys($databc[key($databc)]);
	    	//'chitieunv_id'=>$chitieus
	    	$lks = $this->Phankenh->find('list',array(
	    												'recursive'=>0,'contain'=>array('kenh'),
	    												'conditions'=>array('Phankenh.id'=>$pkbc),
	    												'fields'=>array('Kenh.loaikenh_id'),
	    												'group'=>array('Kenh.loaikenh_id')));//,'ngaykt_hd'=>null
	    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
	    	
	    	$kenhs = $this->Phankenh->find('list',array(
	    											'recursive'=>0,'contain'=>array('Kenh'),
	    											'conditions'=>array('Phankenh.id'=>$pkbc),
	    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
	    												));
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
			$slkh = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('hanghoa_id','soluong')));
	    	
			$this->set(compact('databc','loaikenhs','kenhs','chitieus','dbc','nhanvien','date','slkh','dvcha'));
	    	}
    }

    function kqbhtuannv($donvi_id=null,$nhanvien_id=0)
    {
    	$nhanvien =array();
    	$thang = array();
    	$nams = array();
    	
    	if ($donvi_id!=null)
    	{
    		 // nam
    		$nam = $this->Chitieunv->Kehoach->find('list',array(
	    										'conditions'=>array('donvi_id'=>$donvi_id),
	    										'fields'=>array('ngaybd'),
	    										'group'=>array('YEAR(Kehoach.ngaybd)'),
	    										'order'=>array('ngaybd DESC')));
    		foreach ($nam as $key => $value) 
    		{
    			$nams[date('Y',strtotime($value))]=date('Y',strtotime($value)); 
    		}
    		$namht = date('Y');
    		// lay nhan vien
    		$nhanvien = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id)));
    		// lay thang ke hoach cua nhan vien
	    	$kehoachth = $this->Chitieunv->find('list',array(
	    										'recursive'=>0,'contain'=>array('kehoach'),
	    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'Year(kehoach.ngaybd)'=>$namht),
	    										'fields'=>array('kehoach_id'),
	    										'group'=>array('kehoach_id')));
	    	$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'recursive'=>0,'contain'=>array('Thoigian'),
	    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
	    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id'),
	    												'group'=>array('Thoigian.id'),
	    												'order'=>array('ngaybd DESC')));////'Thoigian.ngaykt'
	    	foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			$thang[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
	    	$this->set(compact('nhanvien','thang','nams','nhanvien_id','donvi_id'));
    	}
    	else
    	{
    		$this->redirect(array('controller'=>'pages','action'=>'home'));
    	}	    	
    }

	function laythang_0()
    {
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvienbh'];
    	$nam = $this->data['Kqbanhang']['nambh'];
    	// lay thang ke hoach cua nhan vien
	    	$kehoachth = $this->Chitieunv->find('list',array(
	    										'recursive'=>0,'contain'=>array('kehoach'),
	    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'YEAR(Kehoach.ngaybd)'=>$nam),
	    										'fields'=>array('kehoach_id'),
	    										'group'=>array('kehoach_id')));
	    	$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'recursive'=>0,'contain'=>array('Thoigian'),
	    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
	    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id'),
	    												'group'=>array('Thoigian.id'),
	    												'order'=>array('Thoigian.ngaybd DESC')));////'Thoigian.ngaykt'
	    	foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			$thang[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
	    $this->set(compact('thang'));
    }
    
    function emptytuan()
    {
    	$this->layout = 'ajax';
    }

    function emptykq()
    {$this->layout='ajax';}

    function laytuan1($nhanvien_id=0)
    {
    	$this->layout='ajax';
	    	if (!empty($this->data['Kqbanhang']['thang']) || !empty($this->data['thang']))
	    	{
	    		if (!empty($this->data['thang']))
		    		$thang_id=$this->data['thang'];
		    	else 
		    		$thang_id=$this->data['Kqbanhang']['thang'];
		    	
		    	$nhanvien_id =$this->data['Kqbanhang']['nhanvien'];
		    	 	
		    	$tg_cha = $this->Thoigian->find('list',array(
		    									'conditions'=>array('id'=>$thang_id),
		    									'fields'=>array('ngaybd','ngaykt')));
		    	$tg = $this->Chitieunv->find('list',array(
		    								'recursive'=>0,
		    								'conditions'=>array('nhanvien_id'=>$nhanvien_id,
		    													'Kehoach.ngaybd >= '=>key($tg_cha),
		    													'Kehoach.ngaykt <=' =>$tg_cha[key($tg_cha)],
		    													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) <='=>7),
		    								'fields'=>array('Kehoach.ngaybd','Kehoach.ngaykt','Kehoach.thoigian_id'),
		    								'group'=>array('Kehoach.ngaybd'),
		    								'order'=>array('ngaybd DESC')));
		    	foreach ($tg as $key => $value):
	    			foreach ($value as $key1 => $value1):
	    				$thoigian[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    			endforeach;
	    		endforeach;    	
	    	}
	    	$this->set(compact('thoigian','nhanvien_id'));    	
    }
    
	function laykqbh1()
    {
    	$this->layout = 'ajax';
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvien'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	if (!empty($this->data['thoigian_id']) || !empty($this->data['thang']) || !empty($this->data['Kqbanhang']['thang']))
    	{
    		if(isset($this->data['thoigian_id']))
    		{
	    		$thoigian_id=$this->data['thoigian_id'];
	    		$tenkh='Chỉ Tiêu Tuần';
    		}
	    	elseif (!empty($this->data['thang'])) 
	    	{
	    		$thoigian_id=$this->data['thang'];
	    		$tenkh='Chỉ Tiêu Tháng';
	    	}
	    	else
	    	{
	    		$thoigian_id=$this->data['Kqbanhang']['thang'];
	    		$tenkh='Chỉ Tiêu Tháng';
	    	}
	    	$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id),));
	    	if (!empty($thoigian))
	    	{
	    		if ($thoigian[0]['Thoigian']['thoigian_id']==0)
					$kh = $this->Kehoach->find('list',array(
											'recursive'=>0,'contain'=>array('Thoigian'),
											'conditions'=>array('Thoigian.thoigian_id'=>$thoigian_id)));
				else 
					$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
		    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
				$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
		    	$data = $this->Kqbanhang->find('all',array(
		    												'recursive'=>0,'contain'=>array('Chitieunv'),
		    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
		    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
		    												'group'=>array('Chitieunv.hanghoa_id','Phankenh.kenh_id')));
		    	$pkbc=array();
		    	for($i=0;$i<count($data);$i++)
		    	{
		    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
			  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
			  		$pkbc[]=$data[$i]['Kqbanhang']['phankenh_id'];
		    	}
		    	//$pkbc = array_keys($databc[key($databc)]);
		    	$lks = $this->Phankenh->find('list',array(
		    												'recursive'=>0,'contain'=>array('kenh'),
		    												'conditions'=>array('Phankenh.id'=>$pkbc),
		    												'fields'=>array('Kenh.loaikenh_id'),
		    												'group'=>array('Kenh.loaikenh_id')));
		    	$loaikenhs = $this->Loaikenh->find('list',array(
		    												'conditions'=>array('id'=>$lks),
		    												'fields'=>array('id','loaikenh')));
		    	
		    	$kenhs = $this->Phankenh->find('list',array(
		    											'recursive'=>0,'contain'=>array('Kenh'),
		    											'conditions'=>array('Phankenh.id'=>$pkbc),
		    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')));
		    	$chitieus = $this->Chitieunv->find('list',array(
		    												'recursive'=>0,'contain'=>array('Hanghoa'),
		    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
		    												));
		    	$slkh = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','soluong')));
	    	}
    	}
    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','slkh','nhanvien_id','nhanvien','tenkh','thoigian_id'));
    }

    
    /*
     *  bao cao tonh hop cua nhan vien
     */
	function kqbanhangnv($nhanvien_id = null)
	{
		//$donvi_id = $this->Session->read('cur_dv');
		//$nhanvien_id =$this->Session->read('cur_manv');
		if(!$nhanvien_id)
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		$donv = $this->Nhanvien->find('first',array('conditions'=>array('Nhanvien.id'=>$nhanvien_id)));
		$donvi_id = $donv['Nhanvien']['donvi_id'];
		$nhanviens = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
														'fields'=>array('Nhanvien.id','Nhanvien.tennv')));
		/*
		 * luy ke
		 */
	   	$nhanvieninfo = $this->Phankenh->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
	   	$luyke=$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>$nhanvien_id)));
	   	$cols[]="Lũy Kế Tuần N";
	   	$cols[]="Kế Hoạch Tuần N";
	   	$cols[]="% Hoàn Thành Tuần";
	   	$cols[]="Kết Quả Tuần N-1";
	   	$cols[]="Lũy Kế Tháng";
	   	$cols[]="Kế Hoạch Tháng";
	   	$cols[]="% Hoàn Thành Tháng";
	   	$rows= array();
	   	for ($i=0;$i<count($luyke);$i++)
	   	{
	   		$rows[$luyke[$i]['Hanghoa']['id']]=$luyke[$i]['Hanghoa']['tenhh'];
	   	}
	   	for($j=0;$j<count($rows);$j++)
	   	{
	   				$i=0;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN'];
	   				$i++;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['khtuan'];
	   				$i++;
	   				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhtuan']).'%';
	   				$i++;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['tongN_1'];
	   				$i++;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN']+$luyke[$j]['Zluykenv']['tongN_1'];
	   				$i++;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['khthang'];
	   				$i++;
	   				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhthang']).'%';
	   				
	  	}
	  	
	   	$this->set(compact('cols','rows','data','nhanvieninfo','nhanviens','nhanvien_id'));
	   	
	   	/*
	   	 * ket qua ban hang thang
	   	 */
	   	$thoigian =array();
    	$nam = $this->Chitieunv->Kehoach->find('list',array(
	    										'conditions'=>array('donvi_id'=>$donvi_id),
	    										'fields'=>array('ngaybd'),
	    										'group'=>array('YEAR(Kehoach.ngaybd)'),
	    										'order'=>array('ngaybd DESC')));
    		foreach ($nam as $key => $value) 
    		{
    			$nams[date('Y',strtotime($value))]=date('Y',strtotime($value)); 
    		}
    		$namht = date('Y');

    		//$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id)));
	    	$kehoachth = $this->Chitieunv->find('list',array(
	    										'recursive'=>0,'contain'=>array('kehoach'),
	    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'YEAR(ngaybd)'=>$namht),
	    										'fields'=>array('kehoach_id')));
	    	$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'recursive'=>0,'contain'=>array('Thoigian'),
	    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
	    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id'),
	    												'order'=>array('Thoigian.ngaybd DESC')
	    												));
	    	
			foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			if(date('m',strtotime($key1))==date('m'))
	    			{
	    				$ngaybd =date('Y-m-d',strtotime($key1));
						$ngaykt = date('Y-m-d',strtotime($value1));
						$thanght = $key;
						$thoigian_id= $thanght;
						$tenkh='Chỉ Tiêu Tháng';
	    			}
	    			$thoigianthang[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
	    	$this->set(compact('thoigianthang','nams','namht'));
    			/*
    			 * ket qua ban hang tuan
    			 */
		    		$thang_id=(isset($thanght)?$thanght:'');
		    	 	
		    	/*$tg_cha = $this->Thoigian->find('list',array(
		    									'conditions'=>array('id'=>$thang_id),
		    									'fields'=>array('ngaybd','ngaykt')));*/
		    	if(isset($ngaybd)&&isset($ngaykt))
		    	{
		    	$tg = $this->Chitieunv->find('list',array(
		    								'recursive'=>0,
		    								'conditions'=>array('nhanvien_id'=>$nhanvien_id,
		    													'Kehoach.ngaybd >= '=>$ngaybd,
		    													'Kehoach.ngaykt <=' =>$ngaykt,
		    													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) <='=>7),
		    								'fields'=>array('Kehoach.ngaybd','Kehoach.ngaykt','Kehoach.thoigian_id'),
		    								'group'=>array('Kehoach.ngaybd'),
		    								'order'=>array('ngaybd DESC')));
		    	foreach ($tg as $key => $value):
	    			foreach ($value as $key1 => $value1):
						if(date('Y-m-d',strtotime($key1))<=date('Y-m-d') && date('Y-m-d',strtotime($value1))>=date('Y-m-d'))
						{
							$ngaybd =date('Y-m-d',strtotime($key1));
							$ngaykt = date('Y-m-d',strtotime($value1));
							$tuanht = $key;
							$thoigian_id = $tuanht;
							$tenkh='Chỉ Tiêu Tuần';
						}
	    				$thoigiantuan[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    			endforeach;
	    		endforeach;    	
	    	$this->set(compact('thoigiantuan','tuanht','thoigian_id','tenkh'));
	    	/*
	    	 * data ket qua ban hang
	    	 */
	    	$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('ngaybd'=>$ngaybd,
	    														'ngaykt'=>$ngaykt)));
		    	$data = $this->Kqbanhang->find('all',array(
		    												'recursive'=>0,'contain'=>array('Chitieunv'),
		    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
		    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
		    												'group'=>array('Chitieunv.hanghoa_id','Phankenh.kenh_id')));
		    	$pkbc=array();
		    	for($i=0;$i<count($data);$i++)
		    	{
		    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
			  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
			  		$pkbc[]=$data[$i]['Kqbanhang']['phankenh_id'];
		    	}
		    	$lks = $this->Phankenh->find('list',array(
		    												'recursive'=>0,'contain'=>array('kenh'),
		    												'conditions'=>array('Phankenh.id'=>$pkbc),
		    												'fields'=>array('Kenh.loaikenh_id'),
		    												'group'=>array('Kenh.loaikenh_id')));
		    	$loaikenhs = $this->Loaikenh->find('list',array(
		    												'conditions'=>array('id'=>$lks),
		    												'fields'=>array('id','loaikenh')));
		    	
		    	$kenhs = $this->Phankenh->find('list',array(
		    											'recursive'=>0,'contain'=>array('Kenh'),
		    											'conditions'=>array('Phankenh.id'=>$pkbc),
		    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')));
		    	$chitieus = $this->Chitieunv->find('list',array(
		    												'recursive'=>0,'contain'=>array('Hanghoa'),
		    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
		    												'order'=>array('hanghoa_id','Hanghoa.tenhh')
		    												));
		    	$slkh = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach'),
	    												'conditions'=>array('Kehoach.ngaybd'=>$ngaybd,
	    																	'Kehoach.ngaykt'=>$ngaykt,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','soluong')));
		    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
		    	}
				$this->set(compact('databc','loaikenhs','kenhs','chitieus','slkh','nhanvien','tenkh','thanght','ngaybd','ngaykt'));
				
				/*
				 * ket qua ban hang ngay
				 */
				
    			$ngay = date('Y-m-d');
	    		$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
	    									));
	    		$dbc = $this->Kqbanhang->find('list',array(
	    											'recursive'=>0,
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('dabaocao'),'group'=>array('dabaocao')));
		    	if (count($dbc)!=0)
		    		$dbc=$dbc[key($dbc)];
		    	else
		    		$dbc=2;
		    	$databc = $this->Kqbanhang->find('list',array(
		    												'recursive'=>0,'contain'=>array('Chitieunv'),
		    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
		    												'fields'=>array('phankenh_id','soluong','Chitieunv.hanghoa_id'),
		    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
		    	$pkbcngay = $this->Kqbanhang->find('list',array(
	    												'recursive'=>0,
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
	    												'fields'=>array('phankenh_id'),
	    												'group'=>array('phankenh_id')));
		    	if (!empty($databc))
		    	{
			    	//$pkbcngay = array_keys($databc[key($databc)]);
			    	
			    	$lkngays = $this->Phankenh->find('list',array(
			    												'recursive'=>0,'contain'=>array('kenh'),
			    												'conditions'=>array('Phankenh.id'=>$pkbcngay),
			    												'fields'=>array('Kenh.loaikenh_id'),
			    												'group'=>array('Kenh.loaikenh_id')));
			    	$loaikenhngays = $this->Loaikenh->find('list',array(
			    												'conditions'=>array('id'=>$lkngays),
			    												'fields'=>array('id','loaikenh')));
			    	
			    	$kenhngays = $this->Phankenh->find('list',array(
			    											'recursive'=>0,'contain'=>array('Kenh'),
			    											'conditions'=>array('Phankenh.id'=>$pkbcngay),
			    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
			    												));
					$chitieungays = $this->Chitieunv->find('list',array(
			    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
			    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
			    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
					$slkhngay = $this->Chitieunv->find('list',array(
		    												'recursive'=>0,'contain'=>array('Kehoach'),
		    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('hanghoa_id','soluong')));
			    	
					$this->set(compact('databc','loaikenhngays','kenhngays','chitieungays','dbc','slkhngay'));
		    	}
					
				
				/*
				 *	thong ke so luong san pham theo kenh 
				 */
				$khtk = $this->Kehoach->find('first',array('conditions'=>array('donvi_id'=>$donvi_id,
																				'Kehoach.ngaybd <='=>date('Y-m-d'),
																				'Kehoach.ngaykt >='=>date('Y-m-d'),
																				'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8)));
				$ngaybdtk = $khtk['Kehoach']['ngaybd'];
				$ngaykttk = $khtk['Kehoach']['ngaykt'];
				
				/*$hhtks = $this->Zthbhkenh->find('list',array(
												'conditions'=>array('Zthbhkenh.ngay >='=>$ngaybdtk,
																	'Zthbhkenh.ngay <='=>$ngaykttk,
																	'Zthbhkenh.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Zthbhkenh.hanghoa_id','Zthbhkenh.tenhh'),
												'group'=>array('Zthbhkenh.hanghoa_id')));*/
				$hhtks = $this->Chitieunv->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id),
															'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
															'group'=>array('Hanghoa.id'),
															'order'=>array('Hanghoa.tenhh')));
				$result = $this->Zthbhkenh->find('all',array(
												'conditions'=>array('Zthbhkenh.ngay >='=>$ngaybdtk,
																	'Zthbhkenh.ngay <='=>$ngaykttk,
																	'Zthbhkenh.nhanvien_id'=>$nhanvien_id,
																	'Zthbhkenh.hanghoa_id'=>array_keys($hhtks)),
												'fields'=>array('Zthbhkenh.kenh_id','Zthbhkenh.tenkenh','Zthbhkenh.hanghoa_id','Zthbhkenh.tenhh','sum(Zthbhkenh.soluong) as soluong'),
												'group'=>array('Zthbhkenh.hanghoa_id','Zthbhkenh.kenh_id')));
				
				$kenhtks = $this->Phankenh->find('list',array(
														'recursive'=>0,
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('Kenh.id','Kenh.tenkenh')));
				$datatk = array();
				$i = 0;
				foreach ($kenhtks as $keykenh => $valuekenh)
				{
					$j=0;
					foreach ($hhtks as $keyhh =>$valuehh)
					{
						foreach ($result as  $value)
						{
							if($value['Zthbhkenh']['kenh_id']==$keykenh &&$value['Zthbhkenh']['hanghoa_id']==$keyhh)
							{
								//$datatk[$keykenh][$keyhh]=$value[0]['soluong'];
								$datatk[$i][$j]=$value[0]['soluong'];
								break;
							} 
						}
						$j++;
					}
					$i++;
				}
				$this->set(compact('ngaybdtk','ngaykttk','kenhtks','hhtks','datatk'));
				
	}
    
	function layluyke()
	{
		/*
		 * luy ke
		 */
		$this->layout = 'ajax';
		$nhanvien_id =$this->data['Kqbanhang']['nhanvien_id'];
	   	$nhanvieninfo = $this->Phankenh->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
	   	$luyke=$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>$nhanvien_id)));
	   	$cols[]="Lũy Kế Tuần N";
	   	$cols[]="Kế Hoạch Tuần N";
	   	$cols[]="% Hoàn Thành Tuần";
	   	$cols[]="Kết Quả Tuần N-1";
	   	$cols[]="Lũy Kế Tháng";
	   	$cols[]="Kế Hoạch Tháng";
	   	$cols[]="% Hoàn Thành Tháng";
	   	$rows= array();
	   	for ($i=0;$i<count($luyke);$i++)
	   	{
	   		$rows[$luyke[$i]['Hanghoa']['id']]=$luyke[$i]['Hanghoa']['tenhh'];
	   	}
	   	for($j=0;$j<count($rows);$j++)
	   	{
	   				$i=0;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN'];
	   				$i++;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['khtuan'];
	   				$i++;
	   				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhtuan']).'%';
	   				$i++;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['tongN_1'];
	   				$i++;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['tuanN']+$luyke[$j]['Zluykenv']['tongN_1'];
	   				$i++;
	   				$data[$j][$i]=$luyke[$j]['Zluykenv']['khthang'];
	   				$i++;
	   				$data[$j][$i]=round($luyke[$j]['Zluykenv']['hoanthanhthang']).'%';
	   				
	  	}
	   	$this->set(compact('cols','rows','data','nhanvieninfo','nhanviens','nhanvien_id'));
	}
	
	function laykqbhthang()
    {
    	$this->layout = 'ajax';
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	if (!empty($this->data['thangthang']) || !empty($this->data['Kqbanhang']['thangthang']))
    	{
	    	if (!empty($this->data['thangthang'])) 
	    	{
	    		$thoigian_id=$this->data['thangthang'];
	    		$tenkh='Chỉ Tiêu Tháng';
	    	}
	    	else
	    	{
	    		$thoigian_id=$this->data['Kqbanhang']['thangthang'];
	    		$tenkh='Chỉ Tiêu Tháng';
	    	}
	    	$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id),));
	    	if (!empty($thoigian))
	    	{
	    		if ($thoigian[0]['Thoigian']['thoigian_id']==0)
					$kh = $this->Kehoach->find('list',array(
											'recursive'=>0,'contain'=>array('Thoigian'),
											'conditions'=>array('Thoigian.thoigian_id'=>$thoigian_id)));
				else 
					$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
		    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
				$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
		    	$data = $this->Kqbanhang->find('all',array(
		    												'recursive'=>0,'contain'=>array('Chitieunv'),
		    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
		    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
		    												'group'=>array('Chitieunv.hanghoa_id','Phankenh.kenh_id')));
		    	$pkbc=array();
		    	for($i=0;$i<count($data);$i++)
		    	{
		    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
			  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
			  		$pkbc[]=$data[$i]['Kqbanhang']['phankenh_id'];
		    	}
		    	//$pkbc = array_keys($databc[key($databc)]);
		    	$lks = $this->Phankenh->find('list',array(
		    												'recursive'=>0,'contain'=>array('kenh'),
		    												'conditions'=>array('Phankenh.id'=>$pkbc),
		    												'fields'=>array('Kenh.loaikenh_id'),
		    												'group'=>array('Kenh.loaikenh_id')));
		    	$loaikenhs = $this->Loaikenh->find('list',array(
		    												'conditions'=>array('id'=>$lks),
		    												'fields'=>array('id','loaikenh')));
		    	
		    	$kenhs = $this->Phankenh->find('list',array(
		    											'recursive'=>0,'contain'=>array('Kenh'),
		    											'conditions'=>array('Phankenh.id'=>$pkbc),
		    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')));
		    	$chitieus = $this->Chitieunv->find('list',array(
		    												'recursive'=>0,'contain'=>array('Hanghoa'),
		    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
		    												));
		    	$slkh = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','soluong')));
	    	}
    	}
    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','slkh','nhanvien_id','nhanvien','tenkh','thoigian_id'));
    }
	 
    function laythangthang()
    {
    	$this->layout = 'ajax';
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
    	$nam = $this->data['Kqbanhang']['namthang'];
    	// lay thang ke hoach cua nhan vien
	    	$kehoachth = $this->Chitieunv->find('list',array(
	    										'recursive'=>0,'contain'=>array('kehoach'),
	    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'YEAR(Kehoach.ngaybd)'=>$nam),
	    										'fields'=>array('kehoach_id'),
	    										'group'=>array('kehoach_id')));
	    	$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'recursive'=>0,'contain'=>array('Thoigian'),
	    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
	    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id'),
	    												'group'=>array('Thoigian.id'),
	    												'order'=>array('Thoigian.ngaybd DESC')));////'Thoigian.ngaykt'

	    	foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			$thang[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
	    $this->set(compact('thang'));
    }
	 
	function laykqbh()
    {
    	//$this->layout = 'ajax';
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	
    	if (isset($this->data['thang']) || isset($this->data['Kqbanhang']['thang']))
    	{
	    	if(isset($this->data['thang']))
	    		$thoigian_id = $this->data['thang'];
	    	else
	    		$thoigian_id = $this->data['Kqbanhang']['thang'];
	    	$tenkh='Chỉ Tiêu Tháng';
	    	if(isset($this->data['tuan']))
	    	{
	    		if($this->data['tuan']!=null)
	    			$thoigian_id = $this->data['tuan'];
	    			$tenkh='Chỉ Tiêu Tuần';
	    	}
	    	else
	    	{
	    		if($this->data['Kqbanhang']['tuan']!=null)
	    			$thoigian_id = $this->data['Kqbanhang']['tuan'];
	    			$tenkh='Chỉ Tiêu Tuần';
	    	}
    	
	    	$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id),));
	    	if (!empty($thoigian))
	    	{
	    		if ($thoigian[0]['Thoigian']['thoigian_id']==0)
					$kh = $this->Kehoach->find('list',array(
											'recursive'=>0,'contain'=>array('Thoigian'),
											'conditions'=>array('Thoigian.thoigian_id'=>$thoigian_id)));
				else 
					$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
		    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
				$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
		    	$data = $this->Kqbanhang->find('all',array(
		    												'recursive'=>0,'contain'=>array('Chitieunv'),
		    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
		    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
		    												'group'=>array('Chitieunv.hanghoa_id','Phankenh.kenh_id')));
		    	
		    	$pkbc=array();
		    	for($i=0;$i<count($data);$i++)
		    	{
		    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
			  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
			  		$pkbc[]=$data[$i]['Kqbanhang']['phankenh_id'];
		    	}
		    	//$pkbc = array_keys($databc[key($databc)]);
		    	$lks = $this->Phankenh->find('list',array(
		    												'recursive'=>0,'contain'=>array('kenh'),
		    												'conditions'=>array('Phankenh.id'=>$pkbc),
		    												'fields'=>array('Kenh.loaikenh_id'),
		    												'group'=>array('Kenh.loaikenh_id')));
		    	$loaikenhs = $this->Loaikenh->find('list',array(
		    												'conditions'=>array('id'=>$lks),
		    												'fields'=>array('id','loaikenh')));
		    	
		    	$kenhs = $this->Phankenh->find('list',array(
		    											'recursive'=>0,'contain'=>array('Kenh'),
		    											'conditions'=>array('Phankenh.id'=>$pkbc),
		    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')));
		    	$chitieus = $this->Chitieunv->find('list',array(
		    												'recursive'=>0,'contain'=>array('Hanghoa'),
		    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
		    												'order'=>array('hanghoa_id','Hanghoa.tenhh')
		    												));
		    	$slkh = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','soluong')));
	    	}
    	}
    	
    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','slkh','nhanvien_id','nhanvien','tenkh','thoigian_id','thoigian'));
    }
	 
	function laythang()
    {
    	$this->layout='ajax';
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
    	$nam = $this->data['Kqbanhang']['nam'];
    	// lay thang ke hoach cua nhan vien
	    	$kehoachth = $this->Chitieunv->find('list',array(
	    										'recursive'=>0,'contain'=>array('kehoach'),
	    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'YEAR(Kehoach.ngaybd)'=>$nam),
	    										'fields'=>array('kehoach_id'),
	    										'group'=>array('kehoach_id')));
	    	$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'recursive'=>0,'contain'=>array('Thoigian'),
	    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
	    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id'),
	    												'group'=>array('Thoigian.id'),
	    												'order'=>array('Thoigian.ngaybd DESC')));////'Thoigian.ngaykt'
	    	foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			$thang[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
	    $this->set(compact('thang'));
    }
	 
 	function laytuan()
    {
    	$this->layout='ajax';
	    	if (isset($this->data['Kqbanhang']['thang']) || isset($this->data['thang']))
	    	{
	    		if (isset($this->data['thang']))
		    		$thang_id=$this->data['thang'];
		    	else 
		    		$thang_id=$this->data['Kqbanhang']['thang'];
		    	
		    	$nhanvien_id =$this->data['Kqbanhang']['nhanvien_id'];
		    	 	
		    	$tg_cha = $this->Chitieunv->find('first',array(
		    								'recursive'=>0,
		    								'conditions'=>array('nhanvien_id'=>$nhanvien_id,
		    													'thoigian_id'=>$thang_id,
		    													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7)));
		    	$tg = $this->Chitieunv->find('list',array(
		    								'recursive'=>0,
		    								'conditions'=>array('nhanvien_id'=>$nhanvien_id,
		    													'Kehoach.ngaybd >= '=>$tg_cha['Kehoach']['ngaybd'],
		    													'Kehoach.ngaykt <=' =>$tg_cha['Kehoach']['ngaykt'],
		    													'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) <='=>7),
		    								'fields'=>array('Kehoach.ngaybd','Kehoach.ngaykt','Kehoach.thoigian_id'),
		    								'group'=>array('Kehoach.ngaybd'),
		    								'order'=>array('ngaybd DESC')));
		    	foreach ($tg as $key => $value):
	    			foreach ($value as $key1 => $value1):
	    				$tuans[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    			endforeach;
	    		endforeach;    	
	    	}
	    	$this->set(compact('tuans','nhanvien_id'));    	
    }
	 
	function kqngaynv11()
    {
    	$this->layout = 'ajax';
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
    	$date = $this->data['Kqbanhang']['ngay'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	$ngay=date('Y-m-d',strtotime($date));
	    	$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
	    									));
	    	$dbc = $this->Kqbanhang->find('list',array(
	    											'recursive'=>0,'contain'=>array('Chitieunv'),
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('dabaocao'),'group'=>array('dabaocao')));
	    	if (count($dbc)!=0)
	    		$dbc=$dbc[key($dbc)];
	    	else
	    		$dbc=2;
	    	$databc = $this->Kqbanhang->find('list',array(
	    												'recursive'=>0,'contain'=>array('Chitieunv'),
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
	    												'fields'=>array('phankenh_id','soluong','Chitieunv.hanghoa_id'),
	    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
	    	if (!empty($databc))
	    	{
	    	$pkbc = array_keys($databc[key($databc)]);
	    	//'chitieunv_id'=>$chitieus
	    	$lks = $this->Phankenh->find('list',array(
	    												'recursive'=>0,'contain'=>array('kenh'),
	    												'conditions'=>array('Phankenh.id'=>$pkbc),
	    												'fields'=>array('Kenh.loaikenh_id'),
	    												'group'=>array('Kenh.loaikenh_id')));//,'ngaykt_hd'=>null
	    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
	    	
	    	$kenhs = $this->Phankenh->find('list',array(
	    											'recursive'=>0,'contain'=>array('Kenh'),
	    											'conditions'=>array('Phankenh.id'=>$pkbc),
	    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
	    												));
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
			$slkh = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('hanghoa_id','soluong')));
	    	debug($databc[key($databc)]);
			$this->set(compact('databc','loaikenhs','kenhs','chitieus','dbc','nhanvien','date','slkh'));
	    	}
      $this->set(compact('nhanvien_id','date'));
    }
	
	function kqngaynv()
    {
    	$this->layout = 'ajax';
    	$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
    	$date = $this->data['Kqbanhang']['ngay'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    	$ngay=date('Y-m-d',strtotime($date));
	    	$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
	    									));
	    	$dbc = $this->Kqbanhang->find('list',array(
	    											'recursive'=>0,'contain'=>array('Chitieunv'),
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('dabaocao'),'group'=>array('dabaocao')));
	    	if (count($dbc)!=0)
	    		$dbc=$dbc[key($dbc)];
	    	else
	    		$dbc=2;
	    	$databc = $this->Kqbanhang->find('list',array(
	    												'recursive'=>0,
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
	    												'fields'=>array('phankenh_id','soluong','Chitieunv.hanghoa_id'),
	    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
	    	$pkbc = $this->Kqbanhang->find('list',array(
	    												'recursive'=>0,
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
	    												'fields'=>array('phankenh_id'),
	    												'group'=>array('phankenh_id')));
	    	
	    	if (!empty($databc))
	    	{
	    	//$pkbc = array_keys($databc[key($databc)]);
	    	//'chitieunv_id'=>$chitieus
	    	$lks = $this->Phankenh->find('list',array(
	    												'recursive'=>0,'contain'=>array('kenh'),
	    												'conditions'=>array('Phankenh.id'=>$pkbc),
	    												'fields'=>array('Kenh.loaikenh_id'),
	    												'group'=>array('Kenh.loaikenh_id')));//,'ngaykt_hd'=>null
	    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
	    	
	    	$kenhs = $this->Phankenh->find('list',array(
	    											'recursive'=>0,'contain'=>array('Kenh'),
	    											'conditions'=>array('Phankenh.id'=>$pkbc),
	    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
	    												));
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
			$slkh = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('hanghoa_id','soluong')));
	    	
			$this->set(compact('databc','loaikenhs','kenhs','chitieus','dbc','nhanvien','date','slkh'));
	    	}
      $this->set(compact('nhanvien_id','date'));
    }
    
	function layketqua()
	{
		$this->layout='ajax';
		$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
		$ngaybd = date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaybd']));
		if(empty($this->data['Kqbanhang']['ngaykt']))
			$ngaykt = date('Y-m-d');
		else 
			$ngaykt  = date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaykt']));
		
		$tg = $this->Chitieunv->find('first',array('recursive'=>0,
													'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																		'Kehoach.ngaybd <='=>$ngaybd,
																		'Kehoach.ngaykt >='=>$ngaykt,
																		'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
													'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
		$kh = $tg['Kehoach']['id'];
		$this->Kehoachbhld->recursive = 1;
		$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
							'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																'thoigian >='=>$ngaybd,
																'thoigian <='=>$ngaykt));
		$this->set('chitieungays', $this->paginate('Kehoachbhld'));
		$hanghoas = $this->Chitieunv->find('list',array('recursive'=>0,
											'conditions'=>array('kehoach_id'=>$kh,
																'Chitieunv.nhanvien_id'=>$nhanvien_id),
											'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));
		
		$this->set(compact('hanghoas','chitieungays'));
	}  

	function laytksoluong()
	{
		$this->layout='ajax';
		$ngaybdtk = date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaybdtksl']));
		$ngaykttk =  date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaykttksl']));
		$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
		if(isset($this->data['Kqbanhang']['kenh_id']))
			if(!empty($this->data['Kqbanhang']['kenh_id']))
			{
				$kenh_id = $this->data['Kqbanhang']['kenh_id'];		
				$kenhtks = $this->Phankenh->Kenh->find('list',array(
											'conditions'=>array('id'=>$kenh_id),
											'fields'=>array('Kenh.id','Kenh.tenkenh')));	
			}
			else
			{
				
				$kenhtks = $this->Phankenh->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																				'ngaykt_hd'=>null),
															'fields'=>array('Kenh.id','Kenh.tenkenh')));
				$kenh_id=array_keys($kenhtks);
			}
		else
			if(!empty($this->data['kenh_id']))
			{
				$kenh_id = $this->data['kenh_id'];		
				$kenhtks = $this->Phankenh->Kenh->find('list',array(
											'conditions'=>array('id'=>$kenh_id),
											'fields'=>array('Kenh.id','Kenh.tenkenh')));	
			}
			else
			{
				
				$kenhtks = $this->Phankenh->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																				'ngaykt_hd'=>null),
															'fields'=>array('Kenh.id','Kenh.tenkenh')));
				$kenh_id=array_keys($kenhtks);
			}
		
		if(!empty($this->data['Kqbanhang']['hanghoa_id']))
		{
			$hh = $this->data['Kqbanhang']['hanghoa_id'];
			$hhtks = $this->Chitieunv->Hanghoa->find('list',array('conditions'=>array('id'=>$hh),
														'fields'=>array('id','tenhh'),
														'order'=>array('Hanghoa.tenhh')));			
		}
		else
		{
			$hhtks = $this->Chitieunv->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id),
															'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
															'group'=>array('Hanghoa.id'),
															'order'=>array('Hanghoa.tenhh')));
		}
		
		$result = $this->Zthbhkenh->find('all',array(
												'conditions'=>array('Zthbhkenh.ngay >='=>$ngaybdtk,
																	'Zthbhkenh.ngay <='=>$ngaykttk,
																	'Zthbhkenh.kenh_id'=>$kenh_id,
																	'Zthbhkenh.hanghoa_id'=>array_keys($hhtks)),
												'fields'=>array('Zthbhkenh.kenh_id','Zthbhkenh.tenkenh','Zthbhkenh.hanghoa_id','Zthbhkenh.tenhh','sum(Zthbhkenh.soluong) as soluong'),
												'group'=>array('Zthbhkenh.hanghoa_id','Zthbhkenh.kenh_id')));

		$kenhtks = $this->Phankenh->find('list',array(
												'recursive'=>0,
												'conditions'=>array('kenh_id'=>$kenh_id,
																	'ngaykt_hd'=>null),
												'fields'=>array('Kenh.id','Kenh.tenkenh')));
		$datatk = array();
		$i = 0;
		foreach ($kenhtks as $keykenh => $valuekenh)
		{
			$j=0;
			foreach ($hhtks as $keyhh =>$valuehh)
			{
				foreach ($result as  $value)
				{
					if($value['Zthbhkenh']['kenh_id']==$keykenh &&$value['Zthbhkenh']['hanghoa_id']==$keyhh)
					{
						//$datatk[$keykenh][$keyhh]=$value[0]['soluong'];
						$datatk[$i][$j]=$value[0]['soluong'];
						break;
					} 
				}
					$j++;
				}
				$i++;
			}
				$this->set(compact('ngaybdtk','ngaykttk','kenhtks','hhtks','datatk'));
	}
	
	function laykkenh()
	{
		$this->layout = 'ajax';
		$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
		$kenhtks = $this->Phankenh->find('list',array(
														'recursive'=>0,
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('Kenh.id','Kenh.tenkenh')));
		$this->set(compact('kenhtks'));
	}

    function kqbhld($nhanvien_id=null)
    {
    	$isAjax=0;
    	if($this->RequestHandler->isAjax())
    	{
    		
    		$this->layout='ajax';
    		$isAjax =1;
			$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];
			$ngaybd = date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaybd']));
			if(empty($this->data['Kqbanhang']['ngaykt']))
				$ngaykt = date('Y-m-d');
			else 
				$ngaykt  = date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaykt']));
			
			$tg = $this->Chitieunv->find('first',array('recursive'=>0,
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'Kehoach.ngaybd <='=>$ngaybd,
																			'Kehoach.ngaykt >='=>$ngaykt,
																			'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
														'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
			
			$kh = $tg['Kehoach']['id'];
			$this->Kehoachbhld->recursive =2;
			$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybd,
																	'thoigian <='=>$ngaykt));
			$this->set('chitieungaybhlds', $this->paginate('Kehoachbhld'));
			/*$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$kh,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));*/
			$hanghoabhlds = $this->Hanghoabhld->find('list',array('fields'=>array('Hanghoabhld.hanghoa_id','Hanghoabhld.tenhh'),'order'=>array('Hanghoabhld.tenhh')));
			$donv = $this->Nhanvien->find('first',array('conditions'=>array('Nhanvien.id'=>$nhanvien_id)));
			$donvi_id = $donv['Nhanvien']['donvi_id'];
			$this->set(compact('hanghoabhlds','isAjax','nhanvien_id','donvi_id'));
    	}
    	else
    	{
	    	$donv = $this->Nhanvien->find('first',array('conditions'=>array('Nhanvien.id'=>$nhanvien_id)));
			$donvi_id = $donv['Nhanvien']['donvi_id'];
            //debug($nhanvien_id);
			$nhanviens = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
															'fields'=>array('Nhanvien.id','Nhanvien.tennv')));
	    	/*
			* ket qua ban hang luu dong
			*/
			
			$ngaybdbhld = date('Y-m-d');
			$ngayktbhld  = date('Y-m-d');
			$tgbhld = $this->Chitieunv->find('first',array('recursive'=>0,
										'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																'Kehoach.ngaybd <='=>$ngaybdbhld,
																'Kehoach.ngaykt >='=>$ngayktbhld,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
											'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
			
			$khbhld = $tgbhld['Kehoach']['id'];
			$this->Kehoachbhld->recursive = 2;
			$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybdbhld,
																	'thoigian <='=>$ngayktbhld));
			$this->set('chitieungaybhlds', $this->paginate('Kehoachbhld'));
			/*$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$khbhld,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));*/
			$hanghoabhlds = $this->Hanghoabhld->find('list',array('fields'=>array('Hanghoabhld.hanghoa_id','Hanghoabhld.tenhh'),'order'=>array('Hanghoabhld.tenhh')));
			$this->set(compact('hanghoabhlds','nhanviens','nhanvien_id','isAjax','donvi_id'));
    	}
    }   
    
    function kqbhldxls($donvi_id=null,$nhanvien_id=null,$ngaybdbhld,$ngayktbhld)
    {
        $this->layout='';
    	   $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$donvi_id),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
	    	$donv = $this->Nhanvien->find('first',array('conditions'=>array('Nhanvien.id'=>$nhanvien_id)));
			$donvi_id = $donv['Nhanvien']['donvi_id'];
			$nhanviens = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
															'fields'=>array('Nhanvien.id','Nhanvien.tennv')));
	    	/*
			* ket qua ban hang luu dong
			*/
			
			$ngaybdbhld = date('Y-m-d',strtotime($ngaybdbhld));
		$ngayktbhld  =  date('Y-m-d',strtotime($ngayktbhld));
			$tgbhld = $this->Chitieunv->find('first',array('recursive'=>0,
										'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																'Kehoach.ngaybd <='=>$ngaybdbhld,
																'Kehoach.ngaykt >='=>$ngayktbhld,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
											'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
			
			$khbhld = $tgbhld['Kehoach']['id'];
			$this->Kehoachbhld->recursive = 2;
			$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybdbhld,
																	'thoigian <='=>$ngayktbhld));
			$this->set('chitieungaybhlds', $this->paginate('Kehoachbhld'));
			/*$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$khbhld,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));*/
			$hanghoabhlds = $this->Hanghoabhld->find('list',array('fields'=>array('Hanghoabhld.hanghoa_id','Hanghoabhld.tenhh'),'order'=>array('Hanghoabhld.tenhh')));
			$this->set(compact('hanghoabhlds','nhanviens','nhanvien_id','isAjax','donvi_id','ngaybdbhld','ngayktbhld'));
    	
    }   
    
    function tksoluong($nhanvien_id=null)
    {
    	$donv = $this->Nhanvien->find('first',array('conditions'=>array('Nhanvien.id'=>$nhanvien_id)));
		$donvi_id = $donv['Nhanvien']['donvi_id'];
		$nhanviens = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
													'fields'=>array('Nhanvien.id','Nhanvien.tennv')));
		
		/*
		*	thong ke so luong san pham theo kenh 
		 */
		$khtk = $this->Kehoach->find('first',array('conditions'=>array('donvi_id'=>$donvi_id,
																		'Kehoach.ngaybd <='=>date('Y-m-d'),
																		'Kehoach.ngaykt >='=>date('Y-m-d'),
																		'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8)));
		
		$hhtks = $this->Chitieunv->find('list',array(
													'recursive'=>0,
													'conditions'=>array('nhanvien_id'=>$nhanvien_id),
													'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
													'group'=>array('Hanghoa.id'),
													'order'=>array('Hanghoa.tenhh')));
			
		$kenhtks = $this->Phankenh->find('list',array(
													'recursive'=>0,
													'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																		'ngaykt_hd'=>null),
													'fields'=>array('Kenh.id','Kenh.tenkenh')));
			
			$this->set(compact('kenhtks','hhtks','nhanviens','nhanvien_id'));
					
    }
 	/*
     * end kq nhan vien tong hop
     */
	
	
	/*
	 * trung tam theo doi kq ban hang nhan vien
	 */
	function theodoikqbanhangnv($donvi_id = null)
	{
		$nhanvientts = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id','tennv')));
		/*
		 * luy ke
		 */
		$nvkh = $this->Zluykenv->find('list',array(
												'recursive'=>0,
												'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
												'fields'=>array('nhanvien_id','nhanvien_id')));
		
		$nhanviens = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('id'=>$nvkh),'fields'=>array('tennv')));
			/*
			 * set col luy ke
			 */
			$i=0;
			foreach ($nhanviens as $keynv =>$valuenv):
				$cols[$keynv][$keynv.'0']="Lũy Kế Tuần N";
	    		$cols[$keynv][$keynv.'1']="Kế Hoạch Tuần N";
	    		$cols[$keynv][$keynv.'2']="% Hoàn Thành Tuần";
	    		$cols[$keynv][$keynv.'3']="Kết Quả Tuần N-1";
	    		$cols[$keynv][$keynv.'4']="Lũy Kế Tháng";
	    		$cols[$keynv][$keynv.'5']="Kế Hoạch Tháng";
	    		$cols[$keynv][$keynv.'6']="% Hoàn Thành Tháng";
			endforeach;
			/*
			 * lay data
			 */
			$data =$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>array_keys($nhanviens),)));
			if(!empty($data))
			{
			/*
			 * sap xep lai row hang hoa
			 */
			for ($i=0;$i<count($data);$i++)
	    	{
	    		$rows[$data[$i]['Hanghoa']['id']]=$data[$i]['Hanghoa']['tenhh'];
	    	}
	    	
	    	/*
			 * sap xep lai data
			 */
	    	$i=0;
	    	$tong =array();
	    	foreach ($nhanviens as $keynv =>$valuenv):
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['nhanvien_id']==$keynv && $data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
		    				$luyketh[$keyhh][$keynv.'0']=$data[$j]['Zluykenv']['tuanN'];
		    				$luyketh[$keyhh][$keynv.'1']=$data[$j]['Zluykenv']['khtuan'];
		    				$luyketh[$keyhh][$keynv.'2']=round($data[$j]['Zluykenv']['hoanthanhtuan']).'%';
		    				$luyketh[$keyhh][$keynv.'3']=$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'4']=$data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'5']=$data[$j]['Zluykenv']['khthang'];
		    				$luyketh[$keyhh][$keynv.'6']=round($data[$j]['Zluykenv']['hoanthanhthang']).'%';
		    			}
		    		}
		    		
		    	}
	    	endforeach;
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
							$tong[$keyhh]=$tong[$keyhh]+ $data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];		    				
		    			}
		    		}
		    		
		    	}
			}
			foreach ($nhanviens as $key => $value)
				$nhanviens[$key] = '<a href="/kqbanhangs/kqbanhangnv/'.$key.'">'.$value .'</a>';
	    	$this->set(compact('cols','rows','nhanviens','luyketh','tong','donvi_id','nhanvientts'));
	    	
	    /*
		*	thong ke so luong san pham theo kenh 
		*/
	    	$nhanvien_id = array_keys($nhanvientts);
				$khtk = $this->Kehoach->find('first',array('conditions'=>array('donvi_id'=>$donvi_id,
																				'Kehoach.ngaybd <='=>date('Y-m-d'),
																				'Kehoach.ngaykt >='=>date('Y-m-d'),
																				'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8)));
				$ngaybdtk = $khtk['Kehoach']['ngaybd'];
				$ngaykttk = $khtk['Kehoach']['ngaykt'];
				
				
				$hhtks = $this->Chitieunv->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id),
															'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
															'group'=>array('Hanghoa.id'),
															'order'=>array('Hanghoa.tenhh')));
				
				$kenhtks = $this->Phankenh->find('list',array(
														'recursive'=>0,
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('Kenh.id','Kenh.tenkenh')));
				$datatk = array();
				$i = 0;
				$this->set(compact('ngaybdtk','ngaykttk','kenhtks','hhtks','datatk'));
		/*
		 * ket qua ban hang luu dong
		 */
				
		/*
		 * tonh hop kq ban hang nv
		 */
			$nam = $this->Chitieunv->Kehoach->find('list',array(
	    										'conditions'=>array('donvi_id'=>$donvi_id),
	    										'fields'=>array('ngaybd'),
	    										'group'=>array('YEAR(Kehoach.ngaybd)'),
	    										'order'=>array('ngaybd DESC')));
    		foreach ($nam as $key => $value) 
    		{
    			$nams[date('Y',strtotime($value))]=date('Y',strtotime($value)); 
    		}
    		$namht = date('Y');
    		$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'conditions'=>array('Kehoach.donvi_id'=>$donvi_id,
    															'YEAR(Kehoach.ngaybd)'=>$nam,
	    														'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>7),
	    													'fields'=>array('Kehoach.ngaybd','Kehoach.ngaykt','Kehoach.thoigian_id'),
	    													'group'=>array('Kehoach.thoigian_id'),
	    													'order'=>array('Kehoach.thoigian_id DESC')));////'Thoigian.ngaykt'
	    	foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			$thang[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
    	$this->set(compact('thang','nams'));
	}

	function laytksoluongdv($donvi_id=0)
	{
		$this->layout = 'ajax';
		$ngaybdtk = date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaybdtksl']));
		$ngaykttk = date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaykttksl']));
		
		if(!empty($this->data['Kqbanhang']['nhanvien_id']))
		{$nhanvien_id = $this->data['Kqbanhang']['nhanvien_id'];}
		else
		{$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));}
		if(!empty($this->data['Kqbanhang']['hanghoa_id']))
		{
			$hh = $this->data['Kqbanhang']['hanghoa_id'];
			$hhtks = $this->Chitieunv->Hanghoa->find('list',array('conditions'=>array('id'=>$hh),
													'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
													'order'=>array('Hanghoa.tenhh')));
		}
		else
		{
			$hhtks = $this->Chitieunv->find('list',array(
													'recursive'=>0,
													'conditions'=>array('nhanvien_id'=>$nhanvien_id),
													'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),
													'group'=>array('Hanghoa.id'),
													'order'=>array('Hanghoa.tenhh')));
		}
		
		
		if(isset($this->data['Kqbanhang']['kenh_id']))
		{
			if(!empty($this->data['Kqbanhang']['kenh_id']))
			{
				$kenh_id = $this->data['Kqbanhang']['kenh_id'];		
				$kenhtks = $this->Phankenh->Kenh->find('list',array(
											'conditions'=>array('id'=>$kenh_id),
											'fields'=>array('Kenh.id','Kenh.tenkenh')));	
			}
			else
			{
				
				$kenhtks = $this->Phankenh->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																				'ngaykt_hd'=>null),
															'fields'=>array('Kenh.id','Kenh.tenkenh')));
				$kenh_id=array_keys($kenhtks);
			}
		}
		else
		{
			if(!empty($this->data['kenh_id']))
			{
				$kenh_id = $this->data['kenh_id'];		
				$kenhtks = $this->Phankenh->Kenh->find('list',array(
											'conditions'=>array('id'=>$kenh_id),
											'fields'=>array('Kenh.id','Kenh.tenkenh')));	
			}
			else
			{
				
				$kenhtks = $this->Phankenh->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																				'ngaykt_hd'=>null),
															'fields'=>array('Kenh.id','Kenh.tenkenh'),
															'order'=>array('Phankenh.nhanvien_id')));
				$kenh_id=array_keys($kenhtks);
			}
		}
		
		$result = $this->Zthbhkenh->find('all',array(
												'conditions'=>array('Zthbhkenh.ngay >='=>$ngaybdtk,
																	'Zthbhkenh.ngay <='=>$ngaykttk,
																	'Zthbhkenh.kenh_id'=>$kenh_id,
																	'Zthbhkenh.hanghoa_id'=>array_keys($hhtks)),
												'fields'=>array('Zthbhkenh.kenh_id','Zthbhkenh.tenkenh','Zthbhkenh.hanghoa_id','Zthbhkenh.tenhh','sum(Zthbhkenh.soluong) as soluong'),
												'group'=>array('Zthbhkenh.hanghoa_id','Zthbhkenh.kenh_id')));
		
		$loaikenh = $this->Loaikenh->Kenh->find('list',array('recursive'=>0,'conditions'=>array('Kenh.id'=>$kenh_id),'fields'=>array('Kenh.id','Loaikenh.loaikenh')));
		$nhanvien = $this->Phankenh->find('list',array(
															'recursive'=>0,
															'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																				'ngaykt_hd'=>null),
															'fields'=>array('Kenh.id','Nhanvien.tennv')));
		
		$col_2[]='Loại kênh';
		$col_2[]='Nhân viên';
		$datatk = array();
		$data_2 = array();
		$i = 0;
		foreach ($kenhtks as $keykenh => $valuekenh)
		{
			$j=0;
			foreach ($hhtks as $keyhh =>$valuehh)
			{
				foreach ($result as  $value)
				{
					if($value['Zthbhkenh']['kenh_id']==$keykenh &&$value['Zthbhkenh']['hanghoa_id']==$keyhh)
					{
						//$datatk[$keykenh][$keyhh]=$value[0]['soluong'];
						$datatk[$i][$j]=$value[0]['soluong'];
						break;
					} 
				}
				
				$j++;
			$data_2[$i][0]= $loaikenh[$keykenh];
			$data_2[$i][1]= $nhanvien[$keykenh];
			}
			$i++;
		}
		$kenhtks = array_values($kenhtks);
		
		
		$this->set(compact('ngaybdtk','ngaykttk','kenhtks','hhtks','datatk','col_2','data_2'));
	}	

	function layketquadv($donvi_id=0)
	{
		$this->layout='ajax';
		
        $nv=array();
		if(!empty($this->data['Kqbanhang']['nhanvientt_id'])){
            
			$nhanvien_id = $this->data['Kqbanhang']['nhanvientt_id'];//$this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
            //array_combine($nv,$nhanvien_id);
            $i=0;
            foreach($nhanvien_id as $n){
                $nv[$i]=$n;
                $i++;
            }
         //   debug($nhanvien_id);
            //debug($nv);
            //  $nv=array_values($nhanvien_id);
   
        }
		else
			$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
		$ngaybdbhld = date('Y-m-d',strtotime($this->data['Kqbanhang']['ngaybdbhld']));
		$ngayktbhld  =  date('Y-m-d',strtotime($this->data['Kqbanhang']['ngayktbhld']));
		$tgbhld = $this->Chitieunv->find('first',array('recursive'=>0,
											'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																'Kehoach.ngaybd <='=>$ngaybdbhld,
																'Kehoach.ngaykt >='=>$ngayktbhld,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
											'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
		$khbhld = $tgbhld['Kehoach']['id'];
		$this->Kehoachbhld->recursive = 2;
		$this->paginate['Kehoachbhld'] = array('order'=>array('thoigian DESC'),
								 'limit' => 150,
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybdbhld,
																	'thoigian <='=>$ngayktbhld));
		$this->set('chitieungaybhlds', $this->paginate('Kehoachbhld'));
		/*$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$khbhld,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));*/
		$hanghoabhlds = $this->Hanghoabhld->find('list',array('fields'=>array('Hanghoabhld.hanghoa_id','Hanghoabhld.tenhh')));
		$this->set(compact('hanghoabhlds','ngaybdbhld','ngayktbhld','nv','donvi_id'));
	}  
	
	function layketquadvxls($donvi_id=0,$ngaybdbhld,$ngayktbhld,$nhanvienttid=array())
	{
		$this->layout='';
        $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$donvi_id),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
		
		if(!empty($nhanvienttid))
			$nhanvien_id = $nhanvienttid;//$this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
		else
			$nhanvien_id = $this->Nhanvien->find('list',array(
                'conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));
		$ngaybdbhld = date('Y-m-d',strtotime($ngaybdbhld));
		$ngayktbhld  =  date('Y-m-d',strtotime($ngayktbhld));
		$tgbhld = $this->Chitieunv->find('first',array('recursive'=>0,
											'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																'Kehoach.ngaybd <='=>$ngaybdbhld,
																'Kehoach.ngaykt >='=>$ngayktbhld,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) >'=>8),
											'fields'=>array('Kehoach.id','Kehoach.ngaybd','Kehoach.ngaykt')));
		$khbhld = $tgbhld['Kehoach']['id'];
		$this->Kehoachbhld->recursive = 2;
		
        $this->set('chitieungaybhlds', $this->Kehoachbhld->find('all',array('order'=>array('thoigian DESC'),
								'conditions'=>array('Kehoachbhld.nhanvien_id'=>$nhanvien_id,
																	'thoigian >='=>$ngaybdbhld,
																	'thoigian <='=>$ngayktbhld))));
		/*$hanghoabhlds = $this->Chitieunv->find('list',array('recursive'=>0,
												'conditions'=>array('kehoach_id'=>$khbhld,
																	'Chitieunv.nhanvien_id'=>$nhanvien_id),
												'fields'=>array('Hanghoa.id','Hanghoa.tenhh'),'order'=>array('Hanghoa.id DESC')));*/
        $hanghoabhlds = $this->Hanghoabhld->find('list',array('fields'=>array('Hanghoabhld.hanghoa_id','Hanghoabhld.tenhh')));

		$this->set(compact('hanghoabhlds'));
	}

	function laythangtt($dv_id=0)
    {    	
    	$nam = $this->data['Kqbanhang']['nam'];
    	// lay thang ke hoach cua nhan vien
	    	$kehoachth = $this->Chitieunv->find('list',array(
	    										'recursive'=>0,
	    										'conditions'=>array('Kehoach.donvi_id'=>$dv_id,'YEAR(Kehoach.ngaybd)'=>$nam),
	    										'fields'=>array('kehoach_id'),
	    										'group'=>array('kehoach_id')));
	    	$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'recursive'=>0,'contain'=>array('Thoigian'),
	    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
	    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id'),
	    												'group'=>array('Thoigian.id'),
	    												'order'=>array('Thoigian.ngaybd DESC')));////'Thoigian.ngaykt'
	    	foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			$thang[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
	    $this->set(compact('thang'));
    }
    
	function laytuantt($donvi_id=0)
    {
    	$nam = $this->data['Kqbanhang']['nam'];
    	
    	if (isset($this->data['Kqbanhang']['thang']))
	    	$thang_id=$this->data['Kqbanhang']['thang'];
	   	else
	   		$thang_id=$this->data['thang'];
	   		   		

	   	$kehoachth = $this->Kehoach->find('first',array(
	   										'recursive'=>-1,
	    									'conditions'=>array('Kehoach.donvi_id'=>$donvi_id,'Kehoach.thoigian_id'=>$thang_id),
	    									'group'=>array('id')));
	   	$ngaybd = $kehoachth['Kehoach']['ngaybd'];
	   	$ngaykt = $kehoachth['Kehoach']['ngaykt'];
    	$tg = $this->Kehoach->find('list',array(
	    										'recursive'=>0,
	    										'conditions'=>array('Kehoach.donvi_id'=>$donvi_id,
    															'Kehoach.ngaybd >='=>$ngaybd,
    															'Kehoach.ngaykt <='=>$ngaykt,
	    														'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) <'=>7),
	    										'fields'=>array('Kehoach.ngaybd','Kehoach.ngaykt','Kehoach.thoigian_id'),
	    										'group'=>array('Kehoach.thoigian_id'),
	    										'order'=>array('Kehoach.ngaybd DESC')));////'Thoigian.ngaykt'
	    foreach ($tg as $key => $value):
	    	foreach ($value as $key1 => $value1):
	    		$tuan[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    	endforeach;
	    endforeach;
	    
    	$this->set(compact('tuan'));
    }
    
	function laykqbhtt($dv_id=0)
    {
    	$this->layout = 'ajax';
    	//if (!empty($this->data['Kqbanhang']['thang'])|| !empty($this->data['thoigian_id'])||!empty($this->data['Kqbanhang']['thang'])|| !empty($this->data['thang']))
    	//{
    		if (isset($this->data['Kqbanhang']['thang']))
	    		$thoigian_id=$this->data['Kqbanhang']['thang'];
	    	else
	    		$thoigian_id=$this->data['thang'];
	    		
	    	if(isset($this->data['Kqbanhang']['tuan']))
	    	{
	    		if($this->data['Kqbanhang']['tuan']!=null)
	    			$thoigian_id=$this->data['Kqbanhang']['tuan'];
	    	}
	    	else
	    	{
	    		if($this->data['tuan']!=null)
	    			$thoigian_id=$this->data['tuan'];
	    	}
	    		
	    	$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id)));
	    	if(!empty($thoigian)){
	    	$nhanviens = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$dv_id),'fields'=>array('id')));
	    	
    		$nbd =$thoigian[0]['Thoigian']['ngaybd'];
	    	$nkt = $thoigian[0]['Thoigian']['ngaykt'];
	    	
	    	if (date('d-m-Y',strtotime($nkt))-date('d-m-Y',strtotime($nbd))>8)
	    	{
	    		$khct = $this->Chitieunv->find('all',array(
	    									'recursive'=>0,
	    									'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,
	    														'Kehoach.donvi_id'=>$dv_id),
	    									'fields'=>array('hanghoa_id','sum(Chitieunv.soluong) as slkh'),
	    									'group'=>array('hanghoa_id'),
	    									'order'=>array('hanghoa_id')));
	    		$tg = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd >= '=>$nbd,'ngaykt <=' =>$nkt,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7),
	    									'fields'=>array('id')));
				$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$tg)));
	    	$title = "Chỉ Tiêu Tháng";
	    	}
			else
			{
				$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
				$khct =$this->Chitieunv->find('all',array(
	    										'recursive'=>0,'contain'=>array('Hanghoa','Nhanvien'),
	    										'conditions'=>array('kehoach_id'=>$kh,'Kehoach.donvi_id'=>$dv_id),
	    										'fields'=>array('hanghoa_id','sum(Chitieunv.soluong) as slkh'),
	    										'group'=>array('hanghoa_id'),
	    										'order'=>array('hanghoa_id')));
			$title = "Chỉ Tiêu Tuần";
			}
			//row
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Hanghoa','Nhanvien'),
	    												'conditions'=>array('kehoach_id'=>$kh,'Nhanvien.donvi_id'=>$dv_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
														'order'=>array('hanghoa_id')
	    												));
	    	//col
	    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
			$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
			
	    	$lkbc = $this->Kqbanhang->find('list',array(
    									'recursive'=>0,
    									'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
    									'fields'=>array('phankenh_id'),
    									'group'=>array('Phankenh.id')));
	    	$lks = $this->Phankenh->find('list',array(
	    										'recursive'=>0,
	    										'conditions'=>array('Phankenh.nhanvien_id'=>$nhanviens,'Phankenh.id'=>$lkbc),
	    										'fields'=>array('Kenh.loaikenh_id'),
	    										'group'=>array('Kenh.loaikenh_id')));
    		$loaikenhs = $loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
    		$loaikenhs[]="Tổng";
    		$loaikenhs[]=$title;
    		$loaikenhs[]="% Hoàn Thành";
    		//data

			$qstr = 'SELECT k.loaikenh_id, ct.hanghoa_id, sum( kq.soluong ) as soluong
					FROM kqbanhangs kq, phankenhs pk, kenhs k, chitieunvs ct, nhanviens nv
					WHERE kq.phankenh_id = pk.id
					AND pk.kenh_id = k.id
					AND kq.chitieunv_id = ct.id
					AND ct.nhanvien_id = nv.id
					AND nv.donvi_id ='.$dv_id.'
					AND kq.ngay between "'.$ngaybd.'" and "'.$ngaykt.'" 
					GROUP BY k.loaikenh_id, ct.hanghoa_id';
			$dt = $this->Kqbanhang->query($qstr);	    	
	    	$row=0;
    		$col=0;
    		if(!empty($dt))
    		{
	    		foreach ($chitieus as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['k']['loaikenh_id'])
	    					{ 
	    						$data[$row][$col]=number_format($dt[$i][0]['soluong'],0,',','.');
	    						$tong +=$dt[$i][0]['soluong'];;
	    					} 
	    				endfor;
	    				$col++;
	    			endforeach;
	    			if($khct[$row][0]['slkh']!= 0)
	    				$data[$row][--$col]=number_format(round($tong*100/$khct[$row][0]['slkh'],2),0,',','.').'%';
	    			else
	    				$data[$row][--$col]='%';
	    			$data[$row][--$col]=$khct[$row][0]['slkh'];
	    			$data[$row][--$col]=number_format($tong,0,',','.');
	    			$row++;
	    		endforeach;
    		}
    	}
    	$this->set(compact('data','loaikenhs','chitieus','dv_id','thoigian_id'));
    }
    /*
     if(!empty($dt))
    		{
	    		foreach ($chitieus as $keyct => $valuect):
	    		$col=0;
	    		$tong=0;
	    			foreach ($loaikenhs as $keylk => $valuelk):
	    				for($i=0;$i<count($dt);$i++):
	    					if ($keyct == $dt[$i]['ct']['hanghoa_id'] && $keylk == $dt[$i]['k']['loaikenh_id'])
	    					{ 
	    						$data[$row][$col]=number_format($dt[$i][0]['soluong'],0,',','.');
	    						$tong +=$data[$row][$col];
	    					} 
	    				endfor;
	    				$col++;
	    			endforeach;
	    			if($khct[$row][0]['slkh']!= 0)
	    				$data[$row][--$col]=number_format(round($tong*100/$khct[$row][0]['slkh'],2),0,',','.').'%';
	    			else
	    				$data[$row][--$col]='%';
	    			$data[$row][--$col]=number_format($khct[$row][0]['slkh'],0,',','.');
	    			$data[$row][--$col]=number_format($tong,0,',','.');
	    			$row++;
	    		endforeach;
    		}
     */

    
	/*
	 * end trung theo doi kq nhan vien
	 */
	
	//HAM NAY KHO NEN COI LAI SAU
    function laykqbhexcel($nhanvien_id,$thoigian_id,$tenkh)
    {   
        $this->layout='';
        $nv=$this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id),
            'fields'=>array('donvi_id','donvi_id'),'recursive'=>-1));
        $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$nv),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
        
        
    //	$this->layout = 'ajax';
    	//$nhanvien_id = $this->data['Kqbanhang']['nhanvien'];
    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    		$thoigian = $this->Thoigian->find('all',array(
	    									'conditions'=>array('id'=>$thoigian_id),));
	    	if (!empty($thoigian))
	    	{
	    		if ($thoigian[0]['Thoigian']['thoigian_id']==0)
					$kh = $this->Kehoach->find('list',array(
											'recursive'=>0,'contain'=>array('Thoigian'),
											'conditions'=>array('Thoigian.thoigian_id'=>$thoigian_id)));
				else 
					$kh = $this->Kehoach->find('list',array(
											'conditions'=>array('thoigian_id'=>$thoigian_id)));
		    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
				$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
		    	$data = $this->Kqbanhang->find('all',array(
		    												'recursive'=>0,'contain'=>array('Chitieunv'),
		    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
		    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
		    												'group'=>array('Chitieunv.hanghoa_id','Phankenh.kenh_id')));
		    	$pkbc=array();
		    	for($i=0;$i<count($data);$i++)
		    	{
		    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
			  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
			  		$pkbc[]=$data[$i]['Kqbanhang']['phankenh_id'];
		    	}
		    	//$pkbc = array_keys($databc[key($databc)]);
		    	$lks = $this->Phankenh->find('list',array(
		    												'recursive'=>0,'contain'=>array('kenh'),
		    												'conditions'=>array('Phankenh.id'=>$pkbc),
		    												'fields'=>array('Kenh.loaikenh_id'),
		    												'group'=>array('Kenh.loaikenh_id')));
		    	$loaikenhs = $this->Loaikenh->find('list',array(
		    												'conditions'=>array('id'=>$lks),
		    												'fields'=>array('id','loaikenh')));
		    	
		    	$kenhs = $this->Phankenh->find('list',array(
		    											'recursive'=>0,'contain'=>array('Kenh'),
		    											'conditions'=>array('Phankenh.id'=>$pkbc),
		    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')));
		    	$chitieus = $this->Chitieunv->find('list',array(
		    												'recursive'=>0,'contain'=>array('Hanghoa'),
		    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
		    												));
		    	$slkh = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','soluong')));
	    	}
    	
    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','slkh','nhanvien_id','nhanvien','tenkh'));
    }

	function kqbhthangnv($donvi_id=null,$nhanvien_id=0)
    {
    	$thoigian =array();
    	$nam = $this->Chitieunv->Kehoach->find('list',array(
	    										'conditions'=>array('donvi_id'=>$donvi_id),
	    										'fields'=>array('ngaybd'),
	    										'group'=>array('YEAR(Kehoach.ngaybd)'),
	    										'order'=>array('ngaybd DESC')));
    		foreach ($nam as $key => $value) 
    		{
    			$nams[date('Y',strtotime($value))]=date('Y',strtotime($value)); 
    		}
    		$namht = date('Y');
    	if ($donvi_id!=null)
    	{
    		$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id)));
	    	$kehoachth = $this->Chitieunv->find('list',array(
	    										'recursive'=>0,'contain'=>array('kehoach'),
	    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'YEAR(ngaybd)'=>$namht),
	    										'fields'=>array('kehoach_id')));
	    	$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'recursive'=>0,'contain'=>array('Thoigian'),
	    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
	    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id')
	    												));////'Thoigian.ngaykt'
	    	foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			$thoigian[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
    	}    	
    	$this->set(compact('thoigian','nams','nhanvien','nhanvien_id'));
    }

    function baocao($donvi_id=null,$nhanvien_id=0)
    {
    	$nhanvien =array();
    	$thang = array();
    	if ($donvi_id!=null)
    	{
    		// lay nhan vien
    		$nhanvien = $this->Kqbanhang->Chitieunv->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id)));
    		// lay thang ke hoach cua nhan vien
	    	$kehoachth = $this->Chitieunv->find('list',array(
	    										'recursive'=>0,'contain'=>array('kehoach'),
	    										'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id),
	    										'fields'=>array('kehoach_id'),
	    										'group'=>array('kehoach_id')));
	    	$tg = $this->Chitieunv->Kehoach->find('list',array(
	    												'recursive'=>0,'contain'=>array('Thoigian'),
	    												'conditions'=>array('Kehoach.id'=>$kehoachth,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) >'=>7),
	    												'fields'=>array('Thoigian.ngaybd','Thoigian.ngaykt','Thoigian.id'),
	    												'group'=>array('Thoigian.id')));////'Thoigian.ngaykt'
	    	foreach ($tg as $key => $value):
	    		foreach ($value as $key1 => $value1):
	    			$thang[$key]= date('d-m-Y',strtotime($key1)) . " - " . date('d-m-Y',strtotime($value1));
	    		endforeach;
	    	endforeach;
    	}
    	$this->set(compact('nhanvien','thang','nhanvien_id','donvi_id'));
    }

	function laykqbaocao()
	{
		$this->layout = 'ajax';
		$chon=$this->data['Kqbanhang']['chon'];
		$nhanvien_id = $this->data['Kqbanhang']['nhanvien'];
		if($chon=='ngay')
		{
    		$date = $this->data['Kqbanhang']['ngay'];
    		$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
    		$ngay=date('Y-m-d',strtotime($date));
	    	$thoigian = $this->Thoigian->find('list',array(
	    									'conditions'=>array('ngaybd <='=>$ngay, 'ngaykt >='=>$ngay,'DATEDIFF(Thoigian.ngaykt,Thoigian.ngaybd) <='=>7)
	    									));
	    	$dbc = $this->Kqbanhang->find('list',array(
	    											'recursive'=>0,'contain'=>array('Chitieunv'),
    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
    												'fields'=>array('dabaocao'),'group'=>array('dabaocao')));
	    	if (count($dbc)!=0)
	    		$dbc=$dbc[key($dbc)];
	    	else
	    		$dbc=2;
	    	$databc = $this->Kqbanhang->find('list',array(
	    												'recursive'=>0,'contain'=>array('Chitieunv'),
	    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay'=>$ngay),
	    												'fields'=>array('phankenh_id','soluong','Chitieunv.hanghoa_id'),
	    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
	    	//'chitieunv_id'=>$chitieus
	    	$lks = $this->Phankenh->find('list',array(
	    												'recursive'=>0,'contain'=>array('kenh'),
	    												'conditions'=>array('Phankenh.nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('Kenh.loaikenh_id'),
	    												'group'=>array('Kenh.loaikenh_id')));
	    	$loaikenhs = $this->Loaikenh->find('list',array(
	    												'conditions'=>array('id'=>$lks),
	    												'fields'=>array('id','loaikenh')));
	    	
	    	$kenhs = $this->Phankenh->find('list',array(
	    											'recursive'=>0,'contain'=>array('Kenh'),
	    											'conditions'=>array('nhanvien_id'=>$nhanvien_id),
	    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
	    												));
			$chitieus = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach','Hanghoa'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','Hanghoa.tenhh')));
			$slkh = $this->Chitieunv->find('list',array(
    												'recursive'=>0,'contain'=>array('Kehoach'),
    												'conditions'=>array('Kehoach.thoigian_id'=>key($thoigian),'nhanvien_id'=>$nhanvien_id),
    												'fields'=>array('hanghoa_id','soluong')));
			$this->set(compact('databc','loaikenhs','kenhs','chitieus','dbc','nhanvien','date','slkh'));
		}
		elseif($chon=='tuan')
		{
	    	$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
	    	if (!empty($this->data['Kqbanhang']['tuan']))
	    	{
	    		if (!empty($this->data['Kqbanhang']['tuan']))
		    		$thoigian_id=$this->data['Kqbanhang']['tuan'];
		    	elseif(!empty($this->data['thoigian_id']))
		    		$thoigian_id=$this->data['thoigian_id'];
		    	else 
		    		$thoigian_id=$this->data['thang'];
		    	$thoigian = $this->Thoigian->find('all',array(
		    									'conditions'=>array('id'=>$thoigian_id),));
		    	if ($thoigian[0]['Thoigian']['thoigian_id']==0)
					$kh = $this->Kehoach->find('list',array(
												'recursive'=>0,'contain'=>array('Thoigian'),
												'conditions'=>array('Thoigian.thoigian_id'=>$thoigian_id)));
				else 
					$kh = $this->Kehoach->find('list',array(
												'conditions'=>array('thoigian_id'=>$thoigian_id)));
		    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
				$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
		    	$data = $this->Kqbanhang->find('all',array(
		    												'recursive'=>0,'contain'=>array('Chitieunv'),
		    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
		    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
		    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
		    	for($i=0;$i<count($data);$i++)
		    	{
		    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
			  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
		    	}
		    	$lks = $this->Phankenh->find('list',array(
		    												'recursive'=>0,'contain'=>array('kenh'),
		    												'conditions'=>array('Phankenh.nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('Kenh.loaikenh_id'),
		    												'group'=>array('Kenh.loaikenh_id')));
		    	$loaikenhs = $this->Loaikenh->find('list',array(
		    												'conditions'=>array('id'=>$lks),
		    												'fields'=>array('id','loaikenh')));
		    	
		    	$kenhs = $this->Phankenh->find('list',array(
		    											'recursive'=>0,'contain'=>array('Kenh'),
		    											'conditions'=>array('nhanvien_id'=>$nhanvien_id),
		    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
		    												));
		    	$chitieus = $this->Chitieunv->find('list',array(
		    												'recursive'=>0,'contain'=>array('Hanghoa'),
		    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
		    												));
		    	$slkh = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','soluong')));
	    	}
	    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','slkh','nhanvien_id','nhanvien'));
		}
		else 
		{
			$nhanvien = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nhanvien_id)));
	    	if (!empty($this->data['Kqbanhang']['tuan']))
	    	{
	    		if (!empty($this->data['thang']))
		    		$thoigian_id=$this->data['tuan'];
		    	elseif(!empty($this->data['thoigian_id']))
		    		$thoigian_id=$this->data['thoigian_id'];
		    	else 
		    		$thoigian_id=$this->data['thang'];
		    	$thoigian = $this->Thoigian->find('all',array(
		    									'conditions'=>array('id'=>$thoigian_id),));
		    	if ($thoigian[0]['Thoigian']['thoigian_id']==0)
					$kh = $this->Kehoach->find('list',array(
												'recursive'=>0,'contain'=>array('Thoigian'),
												'conditions'=>array('Thoigian.thoigian_id'=>$thoigian_id)));
				else 
					$kh = $this->Kehoach->find('list',array(
												'conditions'=>array('thoigian_id'=>$thoigian_id)));
		    	$ngaybd = $thoigian[0]['Thoigian']['ngaybd'];
				$ngaykt = $thoigian[0]['Thoigian']['ngaykt'];
		    	$data = $this->Kqbanhang->find('all',array(
		    												'recursive'=>0,'contain'=>array('Chitieunv'),
		    												'conditions'=>array('Chitieunv.nhanvien_id'=>$nhanvien_id,'ngay >='=>$ngaybd,'ngay <='=>$ngaykt),
		    												'fields'=>array('phankenh_id','SUM(Kqbanhang.soluong) as tong','Chitieunv.hanghoa_id'),
		    												'group'=>array('Chitieunv.hanghoa_id','phankenh_id')));
		    	for($i=0;$i<count($data);$i++)
		    	{
		    		$temp[$data[$i]['Kqbanhang']['phankenh_id']]=$data[$i][0]['tong'];
			  		$databc[$data[$i]['Chitieunv']['hanghoa_id']]=$temp;
		    	}
		    	$lks = $this->Phankenh->find('list',array(
		    												'recursive'=>0,'contain'=>array('kenh'),
		    												'conditions'=>array('Phankenh.nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('Kenh.loaikenh_id'),
		    												'group'=>array('Kenh.loaikenh_id')));
		    	$loaikenhs = $this->Loaikenh->find('list',array(
		    												'conditions'=>array('id'=>$lks),
		    												'fields'=>array('id','loaikenh')));
		    	
		    	$kenhs = $this->Phankenh->find('list',array(
		    											'recursive'=>0,'contain'=>array('Kenh'),
		    											'conditions'=>array('nhanvien_id'=>$nhanvien_id),
		    											'fields'=>array('id','Kenh.tenkenh','Kenh.loaikenh_id')
		    												));
		    	$chitieus = $this->Chitieunv->find('list',array(
		    												'recursive'=>0,'contain'=>array('Hanghoa'),
		    												'conditions'=>array('kehoach_id'=>$kh,'nhanvien_id'=>$nhanvien_id),
		    												'fields'=>array('hanghoa_id','Hanghoa.tenhh'),
		    												));
		    	$slkh = $this->Chitieunv->find('list',array(
	    												'recursive'=>0,'contain'=>array('Kehoach'),
	    												'conditions'=>array('Kehoach.thoigian_id'=>$thoigian_id,'nhanvien_id'=>$nhanvien_id),
	    												'fields'=>array('hanghoa_id','soluong')));
	    	}
	    	$this->set(compact('databc','loaikenhs','kenhs','chitieus','slkh','nhanvien_id','nhanvien'));
		}
		
		$this->set(compact('chon'));
	}

	function luyketonghop($donvi_id=null)
	{
		if ($donvi_id!=null)
		{
			$nvkh = $this->Zluykenv->find('list',array(
												'recursive'=>0,
												'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
												'fields'=>array('nhanvien_id','nhanvien_id')));
			$nhanviens = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('id'=>$nvkh),'fields'=>array('tennv')));
			/*
			 * set col luy ke
			 */
			$i=0;
			foreach ($nhanviens as $keynv =>$valuenv):
				$cols[$keynv][$keynv.'0']="Lũy Kế Tuần N";
	    		$cols[$keynv][$keynv.'1']="Kế Hoạch Tuần N";
	    		$cols[$keynv][$keynv.'2']="% Hoàn Thành Tuần";
	    		$cols[$keynv][$keynv.'3']="Kết Quả Tuần N-1";
	    		$cols[$keynv][$keynv.'4']="Lũy Kế Tháng";
	    		$cols[$keynv][$keynv.'5']="Kế Hoạch Tháng";
	    		$cols[$keynv][$keynv.'6']="% Hoàn Thành Tháng";
			endforeach;
			/*
			 * lay data
			 */
			$data =$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>array_keys($nhanviens),)));
			if(!empty($data))
			{
			/*
			 * sap xep lai row hang hoa
			 */
			for ($i=0;$i<count($data);$i++)
	    	{
	    		$rows[$data[$i]['Hanghoa']['id']]=$data[$i]['Hanghoa']['tenhh'];
	    	}
	    	
	    	/*
			 * sap xep lai data
			 */
	    	$i=0;
	    	$tong =array();
	    	foreach ($nhanviens as $keynv =>$valuenv):
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['nhanvien_id']==$keynv && $data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
		    				$luyketh[$keyhh][$keynv.'0']=$data[$j]['Zluykenv']['tuanN'];
		    				$luyketh[$keyhh][$keynv.'1']=$data[$j]['Zluykenv']['khtuan'];
		    				$luyketh[$keyhh][$keynv.'2']=round($data[$j]['Zluykenv']['hoanthanhtuan']).'%';
		    				$luyketh[$keyhh][$keynv.'3']=$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'4']=$data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'5']=$data[$j]['Zluykenv']['khthang'];
		    				$luyketh[$keyhh][$keynv.'6']=round($data[$j]['Zluykenv']['hoanthanhthang']).'%';
		    			}
		    		}
		    		
		    	}
	    	endforeach;
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
							$tong[$keyhh]=$tong[$keyhh]+ $data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];		    				
		    			}
		    		}
		    		
		    	}
			}
	    	$this->set(compact('cols','rows','nhanviens','luyketh','tong','donvi_id'));
		}
		else
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
	}
	
    function luyketonghopexcel($donvi_id=null)
	{
	   $this->layout='';
       $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=>$donvi_id),
            'fields'=>array('tendvcha','tendv')));
            
		if ($donvi_id!=null)
		{
			$nvkh = $this->Zluykenv->find('list',array(
												'recursive'=>0,
												'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
												'fields'=>array('nhanvien_id','nhanvien_id')));
			$nhanviens = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('id'=>$nvkh),'fields'=>array('tennv')));
			/*
			 * set col luy ke
			 */
			$i=0;
			foreach ($nhanviens as $keynv =>$valuenv):
				$cols[$keynv][$keynv.'0']="Lũy Kế Tuần N";
	    		$cols[$keynv][$keynv.'1']="Kế Hoạch Tuần N";
	    		$cols[$keynv][$keynv.'2']="% Hoàn Thành Tuần";
	    		$cols[$keynv][$keynv.'3']="Kết Quả Tuần N-1";
	    		$cols[$keynv][$keynv.'4']="Lũy Kế Tháng";
	    		$cols[$keynv][$keynv.'5']="Kế Hoạch Tháng";
	    		$cols[$keynv][$keynv.'6']="% Hoàn Thành Tháng";
			endforeach;
			/*
			 * lay data
			 */
			$data =$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>array_keys($nhanviens),)));
			
			/*
			 * sap xep lai row hang hoa
			 */
			for ($i=0;$i<count($data);$i++)
	    	{
	    		$rows[$data[$i]['Hanghoa']['id']]=$data[$i]['Hanghoa']['tenhh'];
	    	}
	    	
	    	/*
			 * sap xep lai data
			 */
	    	$i=0;
	    	$tong =array();
	    	foreach ($nhanviens as $keynv =>$valuenv):
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['nhanvien_id']==$keynv && $data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
		    				$luyketh[$keyhh][$keynv.'0']=$data[$j]['Zluykenv']['tuanN'];
		    				$luyketh[$keyhh][$keynv.'1']=$data[$j]['Zluykenv']['khtuan'];
		    				$luyketh[$keyhh][$keynv.'2']=round($data[$j]['Zluykenv']['hoanthanhtuan']).'%';
		    				$luyketh[$keyhh][$keynv.'3']=$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'4']=$data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'5']=$data[$j]['Zluykenv']['khthang'];
		    				$luyketh[$keyhh][$keynv.'6']=round($data[$j]['Zluykenv']['hoanthanhthang']).'%';
		    			}
		    		}
		    		
		    	}
	    	endforeach;
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
							$tong[$keyhh]=$tong[$keyhh]+ $data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];		    				
		    			}
		    		}
		    		
		    	}
	    	//debug($rows);
	    	//debug($tong);
	    	$this->set(compact('cols','rows','nhanviens','luyketh','tong','donvi_id','tendv'));
		}
		else
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
	}
    
	function luykenv_dv($donvi_id=0)
	{
		
		  $donvis = $this->Donvi->find('list',array('field'=>array('tendv')));
        $this->set(compact('dv_cha','donvis','donvi_id'));
    }
    
    function hienluykenv (){
        $this->layout='ajax';
		$donvi_id =$this->data['Kqbanhangs']['donvi'];
        if ($donvi_id!=null)
		{
			$nvkh = $this->Zluykenv->find('list',array(
												'recursive'=>0,
												'conditions'=>array('Nhanvien.donvi_id'=>$donvi_id),
												'fields'=>array('nhanvien_id','nhanvien_id')));
			$nhanviens = $this->Chitieunv->Nhanvien->find('list',array('conditions'=>array('id'=>$nvkh),'fields'=>array('tennv')));
			/*
			 * set col luy ke
			 */
			$i=0;
			foreach ($nhanviens as $keynv =>$valuenv):
				$cols[$keynv][$keynv.'0']="Lũy Kế Tuần N";
	    		$cols[$keynv][$keynv.'1']="Kế Hoạch Tuần N";
	    		$cols[$keynv][$keynv.'2']="% Hoàn Thành Tuần";
	    		$cols[$keynv][$keynv.'3']="Kết Quả Tuần N-1";
	    		$cols[$keynv][$keynv.'4']="Lũy Kế Tháng";
	    		$cols[$keynv][$keynv.'5']="Kế Hoạch Tháng";
	    		$cols[$keynv][$keynv.'6']="% Hoàn Thành Tháng";
			endforeach;
			/*
			 * lay data
			 */
			$data =$this->Zluykenv->find('all',array('conditions'=>array('nhanvien_id'=>array_keys($nhanviens),)));
			
			/*
			 * sap xep lai row hang hoa
			 */
			
			for ($i=0;$i<count($data);$i++)
	    	{
	    		$rows[$data[$i]['Hanghoa']['id']]=$data[$i]['Hanghoa']['tenhh'];
	    	}
	    	
	    	/*
			 * sap xep lai data
			 */
	    	$i=0;
	    	$tong =array();
	    	foreach ($nhanviens as $keynv =>$valuenv):
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['nhanvien_id']==$keynv && $data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
		    				$luyketh[$keyhh][$keynv.'0']=$data[$j]['Zluykenv']['tuanN'];
		    				$luyketh[$keyhh][$keynv.'1']=$data[$j]['Zluykenv']['khtuan'];
		    				$luyketh[$keyhh][$keynv.'2']=round($data[$j]['Zluykenv']['hoanthanhtuan']).'%';
		    				$luyketh[$keyhh][$keynv.'3']=$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'4']=$data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];
		    				$luyketh[$keyhh][$keynv.'5']=$data[$j]['Zluykenv']['khthang'];
		    				$luyketh[$keyhh][$keynv.'6']=round($data[$j]['Zluykenv']['hoanthanhthang']).'%';
		    			}
		    		}
		    		
		    	}
	    	endforeach;
				foreach ($rows as $keyhh =>$valuehh)
		    	{
		    		$tong[$keyhh]=0;
		    		for($j=0;$j<count($data);$j++)
		    		{
		    			if ($data[$j]['Zluykenv']['hanghoa_id']==$keyhh)
		    			{
							$tong[$keyhh]=$tong[$keyhh]+ $data[$j]['Zluykenv']['tuanN']+$data[$j]['Zluykenv']['tongN_1'];		    				
		    			}
		    		}
		    		
		    	}
	    	//debug($rows);
	    	//debug($tong);
	    	$this->set(compact('cols','rows','nhanviens','luyketh','tong','donvi_id','tendv'));
		}
		else
		{
			$this->redirect(array('controller'=>'pages','action'=>'home'));
		}
    }

    function laynvchuabcbh($donvi_id=null)
	{
		$ngay = date('Y-m-d');
		$nhanvien = $this->Chitieunv->find('list',array(
											'recursive'=>0,'contain'=>array('Kehoach'),
											'conditions'=>array('Kehoach.donvi_id'=>$donvi_id,
																'Kehoach.ngaybd <='=>$ngay,
																'Kehoach.ngaykt >='=>$ngay,
																'DATEDIFF(Kehoach.ngaykt,Kehoach.ngaybd) <'=>8),
											'fields'=>array('nhanvien_id'),
											'group'=>array('nhanvien_id')));
		$ctnv = array_keys($nhanvien);
		$nvdabc = $this->Kqbanhang->find('list',array(
										'recursive'=>0,'contain'=>'Chitieunv',
										'conditions'=>array('chitieunv_id'=>$ctnv,'ngay'=>$ngay,'dabaocao'=>1),
										'fields'=>array('chitieunv_id','Chitieunv.nhanvien_id'),
										'group'=>array('Chitieunv.nhanvien_id')));
		$nvchuabc = array_diff($nhanvien, $nvdabc);
		$infonv = $this->Nhanvien->find('list',array('conditions'=>array('id'=>$nvchuabc)));
		debug($infonv);
		//return $infonv;
	}
	
}
?>